In Focus

Check Constraint in SQL Server 2012

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.

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:

check-statement-in-sql-serverr-2012.jpg

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


insert
 into emp values(8,'d')

Output:

check-statement-in-sql-server.jpg

Dropping the Check Constraint:

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


exec
 sp_help emp

Output:

check-statement-in-sql-serverr-.jpg

Now execute the following command:


alter
 table emp drop constraint CK__emp__empId__1A14E395

Output:

check-statement-in-sql-serverr-2012.jpg

Adding the Check Constraint:


alter
 table emp add check(empiD>15)

Output:

check-statement-in-sql-serverr-2012.jpg

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:

check-statement-in-sql-serverr-2012-.jpg

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.