Beginners Guide to CHECK Constraints in SQL Server
During my schooling, one of my teachers said, “A friend of mine scored 105 on a science test out of 100. I was wondering how it is possible to score 105 out of 100. In this case, the value was inflated. Later, it was revealed that the score was a miscalculation. His score was 97. In some contexts, there is a range of values defined, and no value should exceed the defined range.”
The same concept applies to check constraints in SQL Server. Let us consider the age of an employee as an integer data type for a column in an employee table. One of the employees has entered the number -76 (negative 76) in the Age column. In the context of age, it is not possible to have age in a negative number. SQL server will not raise any error because negative values are acceptable in an integer data type. To prevent this, the Age column should accept only positive numbers. This can be accomplished by adding a check constraint on the Age column for the employee table. Let’s get started.
Create an Employee Table with CHECK Constraints
For company ABC, as per their policies, employees must be 18 or older to work for them. Hence, we will add a check constraint while creating the employee table to make sure the employees’ ages are always greater than or equal to 18.
The condition CHECK(AGE >=18) adds a check constraint on the employee table with a condition where the age of an employee should be greater than or equal to 18.
We will try to insert some records to make sure our check constraint works.
a. Add an employee whose age is 18.
The age has been successfully inserted.
b. Add an employee whose age is 15, which violates the check constraint range.
SQL Server has raised the error below because Age = 15 is below the range defined in the CHECK constraint.
c. Add an employee whose age is 26.
The value has successfully been inserted.
Add a CHECK Constraint on an Existing Table
Now, we want to make sure our Gender column only allows M (male) and F (female) values.
We will create a check constraint.
Let’s insert one more male and female employee.
It was successfully inserted.
Now, we will try inserting another character into the Gender column.
The check constraint has the error below.
Add a CHECK Constraint the WITH NOCHECK Option
What if we have already inserted some values which do not conform to the new check constraint? Suppose in the Salary column we have inserted one wrong entry with a salary as $-2300.00. Now, we want to implement a check constraint so that the Salary column only accepts values greater than zero.
We will try to add a check constraint the WITH NOCHECK option.
First, insert an employee with a salary of -2300.00.
Now try to add a Check constraint as per above syntax.
It fails and gives the error message below because the existing values do not conform to the check constraint condition.
In the case above, we can still create a check constraint using WITH NOCHECK. This will not validate existing data against the new check constraint condition.
Read more on SQL Server from the Tech-Recipes archives.
Follow the reactions below and share your own thoughts.