---

CHECK Constraint

CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database. Its like condition checking before saving data into a column.

---

Using CHECK constraint at Table Level

Example:

Code:

CREATE table Student(
    s_id int NOT NULL CHECK(s_id > 0),
    Name varchar(60) NOT NULL,
    Age int
);

The above query will restrict the s_id value to be greater than zero.

Using CHECK constraint at Column Level

Example:

Code:

ALTER table Student ADD CHECK(s_id > 0);

SQL Server Example: Graphically

CHECK constraint is used to limit the range of the values, that can be entered for a column.


Let's say, we have an integer AGE column, in a table. The AGE in general cannot be less than ZERO and at the same time cannot be greater than 150. But, since AGE is an integer column it can accept negative values and values much greater than 150.


So, to limit the values, that can be added, we can use CHECK constraint. In SQL Server, CHECK constraint can be created graphically, or using a query.

Set expression and if necessary you can change the constraint name also.

Save the table

The following check constraint, limits the age between ZERO and 150.

ALTER TABLE Student
ADD CONSTRAINT CK_Student CHECK (Age > 0 AND Age < 150)

The general formula for adding check constraint in SQL Server:

ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME } CHECK ( BOOLEAN_EXPRESSION )

If the BOOLEAN_EXPRESSION returns true, then the CHECK constraint allows the value, otherwise it doesn't. Since, AGE is a nullable column, it's possible to pass null for this column, when inserting a row. When you pass NULL for the AGE column, the boolean expression evaluates to UNKNOWN, and allows the value.

To drop the CHECK constraint:

ALTER TABLE Student
DROP CONSTRAINT CK_Student

---