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.
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);
Read more on SQL Server from the Tech-Recipes archives.
