Learning Objective
The objective of this SQL Server tutorial is to teach you how to use the CHECK CONSTRAINT SQL clause to control table data.
What is CHECK CONSTRAINT in SQL Server?
CHECK CONSTRAINT is a feature to ensure domain integrity of data. Domain integrity simply means that data in tables should conform to business requirements and real-life expectations. This is achieved through the CHECK CONSTRAINT clause which allows us to place restrictions on the data values in one or more columns in the table. The restriction to limit data values can be specified in the form of a data range, allowing only specific values or excluding specific values.
There are 2 types of CHECK CONSTRAINTS – column constraint and table constraint. A column constraint specifies a condition which evaluates column values and operates in the context of a column. It is possible to specify more than one column constraint on a table. A table constraint on the other hand defines a condition which considers and evaluates multiple column values to enforce the constraint and operates in the context of the table.
Operation
A CHECK CONSTRAINT can be defined while creating a table using the CREATE TABLE statement or any time after table creation using the ALTER TABLE statement. It is possible to specify multiple column constraints or both column and table constraint on a table.
Syntax
The basic syntax of adding a CHECK CONSTRAINT using CREATE TABLE and ALTER TABLE statements and dropping a CHECK CONSTRAINT using ALTER TABLE statement is given below.
CREATE TABLE Syntax
Syntax without constraint name
The following CREATE table statements syntax do not use a constraint name. The first statement specifies the constraints against the columns and the second statement specifies all the constraints together at the end after column definitions. Both ways are correct and acceptable.
CREATE TABLE table_name (column1 DATATYPE, column2 DATATYPE check (column2 condition), column3 DATATYPE check (column3 condition), ………………………………);
CREATE TABLE table_name (column1 DATATYPE, column2 DATATYPE, column3 DATATYPE, ……………………………… check (column2 condition), check (column3 condition));
Syntax with constraint name
The following CREATE table statement defines CHECK CONSTRAINT with constraint name. This is the best practice.
CREATE TABLE table_name (column1 DATATYPE, column2 DATATYPE, column3 DATATYPE, ……………………………… CONSTRAINT constraint_name1 CHECK (column1 condition), CONSTRAINT constraint_name2 CHECK (column2 condition));
ALTER TABLE Syntax (to add constraint)
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column1 condition), CHECK (column2 condition);
ALTER TABLE Syntax (to drop constraint)
ALTER TABLE tablename DROP CONSTRAINT constraint_name;
In this syntax,
- CREATE – clause used to create a database object (like table, index, schema etc.).
- DATATYPE – keyword identifying the type of the data i.e. whether int, string, date etc.
- CONSTRAINT – optional. Used to tell SQL that what follows is an integrity constraint.
- constraint_name – optional. A name to identify the constraint that follows.
- CHECK – clause used to specify an integrity constraint for SQL to evaluate.
- ALTER – clause used to modify a database object (like adding column to a table).
- ADD – clause used to add something to a database object (like adding a column or constraint to a table).
- DROP – clause used to remove something from a database object (like dropping columns or constraints from a table) or to delete a database object altogether (like dropping table).
SQL Server CHECK CONSTRAINT Examples
Let us explore the practical usage and implications of CHECK CONSTRAINT with the help of some examples. We will use a reference table called employees containing company employee information.
1) SQL Server CHECK CONSTRAINT – column constraint example
To explore how column constraints are defined and work we will create the employees table through the following query adding 2 column constraints – one checks the data entered for age and another for salary. Please note that as per best practice I have named both the constraints and added them at the end of the table definition.
CREATE TABLE employees (id INT IDENTITY(20101,1) NOT NULL, name VARCHAR(50) NOT NULL, age TINYINT NOT NULL, join_date DATE NOT NULL, last_date DATE NULL, salary DECIMAL(10,2) NOT NULL, CONSTRAINT agecheck CHECK (age>20), CONSTRAINT salarycheck CHECK (salary>0));
After creating the table, we can check the constraints by executing the below query in SSMS (SQL Server Management Studio) and it will show the constraint definitions at the bottom of the output screen.
EXEC SP_HELP employees;
Now we will insert an employee record with the following query. The query will execute successfully since the constraint conditions for age and salary are met.
INSERT INTO employees
(name, age, join_date, salary)
VALUES ('Shraddha Patel', 29, '2008-10-13', 60000.00);
We can check the result with a SELECT query which will show the record in the table.
| id | name | age | join_date | last_date | salary |
| 20101 | Shraddha Patel | 29 | 10/13/2008 | NULL | 60000 |
Now we will try to insert another record with the following query violating the check constraints for age and salary. The query will fail execution citing ‘agecheck’ constraint violation as below.
INSERT INTO employees
(name, age, join_date, salary)
VALUES ('Rajeev Verma', 19, '2018-01-10', 40000);
Msg 547, Level 16, State 1, Server TEST-DESERVER, Line 1
The INSERT statement conflicted with the CHECK constraint "agecheck". The conflict occurred in database "master", table "dbo.employees", column 'age'.
The statement has been terminated.
We will rerun the same query again but this time we will violate the constraint specified for salary. Again, the query will fail execution but this time citing salarycheck constraint violation as below.
INSERT INTO employees
(name, age, join_date, salary)
VALUES ('Rajeev Verma', 23, '2018-01-10', 0);
Msg 547, Level 16, State 1, Server TEST-DBSERVER, Line 1
The INSERT statement conflicted with the CHECK constraint "salarycheck". The conflict occurred in database "master", table "dbo.employees", column 'salary'.
The statement has been terminated.
2) SQL Server CHECK CONSTRAINT – table constraint example
Before going to table constraint, we will drop all the constraints with the following ALTER query.
ALTER TABLE employees DROP CONSTRAINT agecheck, salarycheck;
Now we will run the above failed query for employee Rajeev Verma (with salary 0) and it will run successfully and add the record since the constraints have been dropped.
INSERT INTO employees
(name, age, join_date, salary)
VALUES ('Rajeev Verma', 23, '2018-01-10', 0);
We can verify by running a SELECT query which will show the record in the table.
| id | name | age | join_date | last_date | salary |
| 20101 | Shraddha Patel | 29 | 10/13/2008 | NULL | 60000 |
| 20104 | Rajeev Verma | 23 | 10/01/2018 | NULL | 0 |
Now we add back the constraints with new names and also add a table constraint with the following ALTER query. The table constraint will check and compare the value of last_date with join_date and ensure that the last_date is after the join_date. So, it will evaluate 2 column values to reach at a decision and that is why it is a table constraint.
ALTER TABLE employees ADD CONSTRAINT validate_age check (age>22), CONSTRAINT validate_salary check (salary>0), CONSTRAINT validate_relieving check (last_date>join_date);
We can check whether all the constraints have been added executing the following query.
EXEC SP_HELP employees;
It will show the constraint definitions at the bottom of the output screen as below.
With the table constraint to check and ensure that relieving is after joining in place we will run the following query to insert an employee record violating the constraint. The query will fail execution with the below error which shows that the constraint is working.
INSERT INTO employees
(name, age, join_date, last_date, salary)
VALUES ('Mitali Baruah', 27, '2017-11-30', '2017-01-31', 33000.00);
Msg 547, Level 16, State 1, Server TEST-DBSERVER, Line 1
The INSERT statement conflicted with the CHECK constraint "validate_relieving". The conflict occurred in database "master", table "dbo.employees".
The statement has been terminated.