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 explaining 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

SQL
CREATE TABLE tableName (
    Column1 dataType CHECK (expression),
    Column2 dataType,
    ColumnN dataType
);

Example

SQL
CREATE TABLE emp (
    empId INT CHECK (empId > 10),
    empName VARCHAR(15)
);

Output

Output

If we want to insert a record with less than 10 IDs then it shows the error.

SQL
INSERT INTO emp (empId, empName) 
VALUES (8, 'd');

Output

Message

Dropping the Check Constraint

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

SQL
EXEC sp_help emp;

Output

Result

Now execute the following command.

SQL
ALTER TABLE emp 
DROP CONSTRAINT CK__emp__empId__1A14E395;

Output

Command

Adding the Check Constraint

SQL
ALTER TABLE emp 
ADD CHECK (empId > 15);

Output

SQL Server

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

SQL
INSERT INTO emp 
VALUES (NULL, 'd');

Output

Null

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.