How do I script SQL constraint for a number to fall within a range?

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

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

Answer 2

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

Answer 3

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)

Answer 4

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?