Mastering CHECK Constraints in SQL: A Comprehensive Guide
Table of Content:
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:
<span class="pln"> CREATE table </span><span class="typ">Student</span><span class="pun">(</span><span class="pln"> s_id </span><span class="kwd">int</span><span class="pln"> NOT NULL CHECK</span><span class="pun">(</span><span class="pln">s_id </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pun">),</span><span class="pln"> </span><span class="typ">Name</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">60</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Age</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="pun">);</span><span class="pln"> </span>
The above query will restrict the s_id value to be greater than zero.
Using CHECK constraint at Column Level
Example:
Code:
<span class="pln"> ALTER table </span><span class="typ">Student</span><span class="pln"> ADD CHECK</span><span class="pun">(</span><span class="pln">s_id </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pun">);</span><span class="pln"> </span>
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.
<span class="pln"> ALTER TABLE </span><span class="typ">Student</span><span class="pln"> ADD CONSTRAINT CK_Student CHECK </span><span class="pun">(</span><span class="typ">Age</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pln"> AND </span><span class="typ">Age</span><span class="pln"> </span><span class="pun"><</span><span class="pln"> </span><span class="lit">150</span><span class="pun">)</span><span class="pln"> </span>
The general formula for adding check constraint in SQL Server:
<span class="pln"> ALTER TABLE </span><span class="pun">{</span><span class="pln"> TABLE_NAME </span><span class="pun">}</span><span class="pln"> ADD CONSTRAINT </span><span class="pun">{</span><span class="pln"> CONSTRAINT_NAME </span><span class="pun">}</span><span class="pln"> CHECK </span><span class="pun">(</span><span class="pln"> BOOLEAN_EXPRESSION </span><span class="pun">)</span><span class="pln"> </span>
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:
<span class="pln"> ALTER TABLE </span><span class="typ">Student</span><span class="pln"> DROP CONSTRAINT CK_Student </span>