How can I use check constraint in sql server 2005

Question

i want to check for a particular set of values.
eg

• check columnname should be between 1 to 5
• check columnname should be either 1 or 2 or 4

Answer 1

There is quite a wealth of information in the SQL Server documentation on this, but the two statements to create the check constraints you ask for are:

ALTER TABLE tablename ADD CONSTRAINT constraintName CHECK (colname between 1 and 5);

ALTER TABLE tablename ADD CONSTRAINT constraintName CHECK (colname in (1,2,4));

The condition of a check constraint can include:

1. A list of constant expressions introduced with in

2. A range of constant expressions introduced with between

3. A set of conditions introduced with like, which may contain wildcard characters

This allows you to have conditions like:

(colname >= 1 AND colname <= 5)

Answer 2

ALTER TABLE tablename ADD CONSTRAINT constraintName CHECK (colname in (1,2,4));