Beginners Guide to CHECK Constraints in SQL Server

 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.

IF OBJECT_ID (N'Employee' , N'U' ) IS NOT NULL
DROP TABLE Employee;

CREATE TABLE Employee
(
EmployeeId INT PRIMARY KEY ,
Salary Numeric( 18,2 ),
Gender Char( 1),
Age    INT CHECK  (AGE >= 18) --Check Constraint Condition
);

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.

INSERT INTO Employee   (EmployeeId, Name, Salary , Gender, Age)   
Values ( 100, 'Niraj', 67000.00 , 'M' , 18);

(1 row(s) affected)

The age has been successfully inserted.

b. Add an employee whose age is 15, which violates the check constraint range.

INSERT INTO Employee (EmployeeId, Name, Salary , Gender, Age)
Values(101,'Chetan',56000.00,'M',15);

SQL Server has raised the error below because Age = 15 is below the range defined in the CHECK constraint.

Msg 547, Level 16, State 0, Line 13
The INSERT statement conflicted with the CHECK constraint "CK__Employee__Age__3B0BC30C". The conflict occurred in database "VishLearningDB", table "dbo.Employee", column 'Age'.
The statement has been terminated.

c. Add an employee whose age is 26.

INSERT INTO Employee (EmployeeId, Name, Salary , Gender, Age)
Values(103,'Mani',329999.00,'F',15); 
     
(1 row(s) affected)

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.

Syntax

ALTER TABLE <TableName>
ADD    Constraint <Constraint_Name>
CHECK (<Check_Constraint_Condition>)

Example

ALTER TABLE Employee
ADD    Constraint CK_Employee_Gender
CHECK (Gender = 'M' OR GENDER = 'F' );

Let’s insert one more male and female employee.

INSERT INTO Employee ( EmployeeId, Name , Salary , Gender, Age )
Values(104 ,'Meera', 23000.00,'F' ,26);

INSERT INTO Employee ( EmployeeId, Name , Salary , Gender, Age )
Values(105 ,'Shailesh', 24000.00,'M' ,28);

(1 row(s) affected)

(1 row(s) affected)

It was successfully inserted.

Now, we will try inserting another character into the Gender column.

INSERT INTO Employee ( EmployeeId, Name , Salary , Gender, Age )
Values(105 ,'Vish', 24000.00,'V' ,28);

The check constraint has the error below.

Msg 547, Level 16, State 0, Line 31
The INSERT statement conflicted with the CHECK constraint "CK_Employee_Gender".
The conflict occurred in database "master", table "dbo.Employee", column 'Gender'.
The statement has been terminated.

 

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.

INSERT INTO Employee ( EmployeeId, Name , Salary , Gender, Age )
Values(105 ,'Vish', -2300.00,'M' ,28);

Now try to add a Check constraint as per above syntax.

ALTER TABLE Employee
ADD    Constraint CK_Employee_Salary
CHECK (Salary > 0);

It fails and gives the error message below because the existing values do not conform to the check constraint condition.

The ALTER TABLE statement conflicted with the CHECK constraint "CK_Employee_Salary".
The conflict occurred in database "VishLearningDB", table "dbo.Employee", column 'Salary'.

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.

ALTER TABLE Employee WITH NOCHECK
ADD    Constraint CK_Employee_Salary
CHECK (Salary > 0);