How do I script SQL constraint for a number to fal

问题:

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?

回答1:

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

回答2:

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

回答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)

回答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?