The check constraint in SQL Server allows us to validate data values that are being inserted or updated in one or more columns. If the validation fails, then SQL Server will not insert or update the data.
Table of Contents
Need for Check Constraint
Take a look at the following query. The Salary of an employee must not be a negative number, but still, we are able to insert a negative value.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , Salary Decimal(18,2) NOT NULL , Primary Key (EmployeeID) ) insert into Employee values (1,'Olive Yew',-1000) Select * From Employee **Result ** EmployeeID Name Salary ---------- ------------ -------- 1 Olive Yew -1000.00 |
We can stop users from inserting NULL values using NOT NULL Constraints. But we also need to stop them from inserting invalid values. This is where Check Constraint comes in handy.
We specify the validation rules in Check Constraint. SQL Server checks these rules are against the values that the user inserts or updates It either accepts or rejects them based on the outcome of the validation checks.
Creating Check Constraint
The Syntax of the Check Constraint is as follows. It starts with the keyword CHECK followed by logical_expression which must return true for the validation to succeed.
1 2 3 | CHECK ( logical_expression ) |
In the following query, we create a check constraint on the salary & name fields. The checks if the salary is greater than 0 and the length of the name must be greater than 15.
1 2 3 4 5 6 7 8 9 | CREATE TABLE Employee ( EmployeeID int , Name varchar(50) CHECK (len(Name) > 15), Salary Decimal(18,2) NOT NULL CHECK (Salary > 0) , Primary Key (EmployeeID), ) |
Inserting a row name with less than 15 char will result in an error
1 2 3 4 5 6 7 8 | insert into Employee values (1,'Olive Yew',1000) --- Error --- The INSERT statement conflicted with the CHECK constraint "CK__Employee__Name__11D4A34F". --- The conflict occurred in database "DB", table "dbo.Employee", column 'Name'. |
Inserting a row with salary in negative also result in an error.
1 2 3 4 5 6 7 | insert into Employee values (1,'Olive Yewwwwwwwwwwwwww',-1000) --- Error --- The INSERT statement conflicted with the CHECK constraint "CK__Employee__Salary__1881A0DE". --- The conflict occurred in database "DB", table "dbo.Employee", column 'Salary'. |
Query succeeds only if it satisfies both conditions.
1 2 3 | insert into Employee values (1,'Olive Yewwwwwwwwwwwwww',1000) |
Check Constraint at Table level
You can also apply the Check Constraint at table level. The Syntax of that is as follows.
It starts with the Keyword CONSTRAINT followed by name of the constraint. Then followed by CHECK and logical expression inside the bracket. Note that [ CONSTRAINT constraint_name ] is optional.
1 2 3 | [ CONSTRAINT constraint_name ] CHECK ( logical_expression ) |
The following query creates the check constraint without naming it.
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , Salary Decimal(18,2) NOT NULL , Primary Key (EmployeeID), CHECK (len(Name) > 15), CHECK (Salary > 0), ) |
Naming the Check Constraint
You can also name the constraint. This makes it easier to later drop the constraint of no longer needed. Note that constraint names must be unique across the database. Also, make sure you follow a proper naming convention when naming the constraint. Here we use the CK_<TableName>_<ColumnsName> naming convention.
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , Salary Decimal(18,2) NOT NULL , Primary Key (EmployeeID), CONSTRAINT CK_Employee_Name CHECK (len(Name) > 15), CONSTRAINT CK_Employee_Salary CHECK (Salary > 0), ) |
Complex Constraint Expressions
The following check constraint example shows how you can merge two conditions into a single check constraint using an AND Operator. You can refer to the other columns in the check constraint.
1 2 3 4 5 6 7 8 9 | CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , Salary Decimal(18,2) NOT NULL , Primary Key (EmployeeID), CONSTRAINT CK_Employee CHECK (len(Name) > 15 And Salary > 0), ) |
1 2 3 | CONSTRAINT CK_Employee CHECK ( Salary > 5000 And Salary < 15000) |
NULL & Check Constraints
The Presence of NULL in a check constraint expression will force check constraint returning true.
In the following example, we try to insert null into the salary column.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , Salary Decimal(18,2) , Primary Key (EmployeeID), CHECK (Salary > 0), ) insert into Employee values (1,'Olive Yew',null) Select * from Employee EmployeeID Name Salary ---------- --------- --------- 1 Olive Yew NULL |
Finding List of Check Constraints
You can use the following query to find out the list of all check constraints in the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | select con.[name] as constraint_name, schema_name(t.schema_id) + '.' + t.[name] as [table], col.[name] as column_name, con.[definition], case when con.is_disabled = 0 then 'Active' else 'Disabled' end as [status] from sys.check_constraints con left outer join sys.objects t on con.parent_object_id = t.object_id left outer join sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id order by con.name |
Drop the Check Constraint
Dropping a check constraint is similar to dropping any other constraint. First, find out the name of the check constant using the query from the previous section and use the Alter table Drop constraint query to delete it.
1 2 3 | Alter table TableB drop CONSTRAINT <constraint_name> |
Add Check Constraint to existing Table
You can use the Alter table query to add the Check constraint to the existing table.
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , Salary Decimal(18,2) , Primary Key (EmployeeID), ) Alter Table Employee ADD CONSTRAINT CK_Employee Check (Salary > 0) |
Rules & Limitations
- Expession consting of
NULLvalues are never checked. - You can use user defined functions in check constraint
- Check constraint can refer to columns in the same table. You cannot refer columns from other tables. (But you can create a user defined function where you can use the query)
- You cannot define a check constaint on View
User-Defined Functions in Check Constraint
You can use user-defined functions (UDF) in check constraints. But the functions must return a scalar value.
The MinSalary UDF below returns hard-coded values of 1000. But you can as well use a query to get the value from another table and return it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | -- UDF MinSalary CREATE FUNCTION MinSalary() RETURNS int AS BEGIN DECLARE @retval int SELECT @retval = 1000 -- You can use a query to get the Min Salary RETURN @retval END; GO CREATE TABLE Employee ( EmployeeID int , Name varchar(50) , Salary Decimal(18,2) , Primary Key (EmployeeID), ) -- Using MinSalary UDF in check constraint. Alter Table Employee ADD CONSTRAINT CK_Employee Check (Salary > dbo.MinSalary()) -- Error insert into Employee values (1,'Olive Yeww',1000) The INSERT statement conflicted with the CHECK constraint "CK_Employee". The conflict occurred in database "DB", table "dbo.Employee", column 'Salary'. -- Ok insert into Employee values (1,'Olive Yeww',1001) |
