Set a minimum value column constraint?

[Nagrom]

Is it possible to set a constraint on a database column to only allow values greater than a certain number, even if the data type has a greater capacity? For example, if I have a money field that I only want to hold positive values, can I set that as a constraint in the DB even though the money data type can hold negative values? Programmatic enforcement of the rule is not a possibility because we have users that have direct access to the data via Microsoft Acces (heinous, I know, and I am lobbying to get that changed, but this is all I have to work with at the moment).

 

[jbenson001]

You can add a check constraint. Open EM and the table you want to modify. Select the column. Click the manage Index/Keys button(second from the right on the toolbar). Click on the check constrains tab. Click New. Name the constraint. For the constraint expression use:

<your column> < 0

Click close and save the table.

Jim

 

[Nagrom]

You are a gentleman (or woman) and a scholar! Thanks for the assist.

 

[SQLDenis]

Shouldn't that be > instead of <?

BTW don't use the money data type, it is unreliable use decimal/numeric instead

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Nagrom]

SQLDenis,

Do you have any links to information about problems with the money data type? We have a fair number of legacy data tables that use it so I need to see if the issues with it are things that would be applicable to our data.

Thanks.

 

[SQLDenis]

Run this and you will understand the problem

DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 NUMERIC(19,4),
@num2 NUMERIC(19,4),
@num3 NUMERIC(19,4),
@num4 NUMERIC(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS money_result,
@num4 AS numeric_result

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

Shouldn't that be > instead of <?

Yep, my bad...

also Denis.. good example on the $ thing... I have had this question in the past and was looking for a good example

Jim

 

[Nagrom]

We're only using the money data type on fields that actually hold monetary data and we're not doing any complex calculations with them so the precision is sufficient for what we have it used for, but that's definitely something good to keep in mind for future development.