How do I script SQL constraint for a number to fal

Question

Using SQL Server, how do I script a constraint on a field in a table, so that the acceptable range of values is between 0 and 100?

Answer 1

A check constraint like "fieldname BETWEEN 0 AND 100" should do it.

Answer 2

According to me, the right question isn't "how" but "why".

This 0-100 rule sounds to me like a business rule. Why should it be implemented on the server/database side? If an incorrect value is entered, who will get the error message?

If the user gets the error message, wouldn't it be easier to have the code giving him the message before the transaction reaches the server?

What about range modification? May the rule change? I guess yes: rules ALLWAYS change. Can the range be updated from 0-100 to 101-200? In this case, what about values already entered in the database?

Answer 3

ALTER TABLE Table
ADD CONSTRAINT CK_Table_Column_Range CHECK (
   Column >= 0 AND Column <= 100 --Inclusive
)

Answer 4

Try:

ALTER TABLE myTableName
ADD CONSTRAINT myTableName_myColumnName_valZeroToOneHundred
CHECK (myColumnName BETWEEN 0 AND 100)

This check would be inclusive - here is some info about BETWEEN from MSDN: BETWEEN (Transact SQL)