Check Constraint in SQL Server 2012

Introduction

In this article I describe Check Constraints on a table, how to add a Check Constraint to a table, how to remove a Check Constraint from a table and the limitations of a Check Constraint. Before explainging Check Constraints it is necessary to first explain what Domain Integrity is.

Domain Integrity:

Domain Integrity ensures that values are valid for columns and prevents invalid values for columns within a database.

Check Constraint:

A Check Constraint is a rule that identifies valid values for columns of data. A Check Constraint helps to enforce Domain Integrity. If the condition in a Check Constraint is not satisfied then it prevents the value from entering into the database.

Syntax:

Create table tableName(Column1 dataType Check(expression), Column2,columnN)

Example:

create table emp(empId int check(empId >10),empName varchar(15))

Output:



If we want to insert a record with less then 10 Id then it shows the error:

insert into emp values(8,'d')

Output:



Dropping the Check Constraint:

First of all we can determine the name of the constraint using the following command:

exec sp_help emp

Output:



Now execute the following command:

alter table emp drop constraint CK__emp__empId__1A14E395

Output:



Adding the Check Constraint:

alter table emp add check(empiD>15)

Output:



Limitation:

The Check Constraint rejects the values that are invalid or we can say which does not satisfy the Check Conditions. But in the case of a null, a Check Constraint will allow it to be insert into the database.

Insertion of Null value:

Insert into emp values(Null,'d')

Output:



Summary:
In this article I described a Check Constraint in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.