Check Constraint in SQL Server

In this tutorial, I am going to explain you about Check Constraint in SQL Server. We are already familiar with Primary Key Constraint and Foreign Key Constraint in SQL Server.

After Completing this tutorial you will be able to understand:

• What is Check Constraint in SQL Server?
• How to Create Check Constraint in SQL Server?

Introduction:

A Check Constraint is used to specify a condition on a row in a table. A Check Constraint must refer to only columns of that table. It cannot refer to columns of other tables. We can define a Check Constraint either using CREATE TABLE statement or be using ALTER TABLE Statement.

The syntax for defining a Check Constraint:

CREATE TABLE tablename

(

column1 datatype [ NULL | NOT NULL ],

column2 datatype [ NULL | NOT NULL ],

...

CONSTRAINT check_constraintname

CHECK [ NOT FOR REPLICATION ] (column_name condition)

);

Where;

tablename specifies the name of the table.

column1, column2 specifies the name of the columns of the tablename.

check_constraintname specifies the name of Check Constraint.

column_name is the column on which you want to create your Check Constraint.

condition is the condition that must be met to succeed Check Constraint.

Example:

Suppose you have a condition that your table should accept Employee Records that have age between 18 to 40. Then in such situation, we can create a Check Constraint on Age column. Here is the example for such scenario:

CREATE TABLE Employee

(

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Age INT,

Gender VARCHAR(10),

Salary FLOAT,

CONSTRAINT ageConstraint CHECK(AGE BETWEEN 18 AND 40)

)

You can see in the following CREATE TABLE Statement we have a created a Constraint called ageConstraint on Age column that will accept the value between 18 and 40. Let’s this Check Constraint with a Record Insertion. Run the following query.

INSERT INTO Employee (EmployeeID,FirstName,LastName,Age,Gender,Salary) VALUES (1,'Arvind','Kumar',50,'Male'1,10000)

If you run above query you will get the following error.

Example 2:

Let’s create another example for Constraint. This time I want to create a Check Constraint for Gender Column. The value for Gender column should be Male or Female.

CREATE TABLE Customer

(

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Age INT,

Gender VARCHAR(10),

Salary FLOAT,

CONSTRAINT gender CHECK(Gender=’Male’ 18 AND Gender=’Female’)

)

Try to insert the record with Gender value other than Male and Female.

INSERT INTO Employee (EmployeeID,FirstName,LastName,Age,Gender,Salary) VALUES (1,'Arvind','Kumar',50,'Other',10000)

You will get the following error after running above query.

Create Check Constraint After Table Creation:

You can also create Check Constraint using ALTER TABLE Statement.

The syntax for creating Check Constraint using ALTER TABLE Statement:

ALTER TABLE tablename
ADD CONSTRAINT constraintname
CHECK (columnname condition);

Where;

tablename specifies the name of the table.

check_constraintname specifies the name of Check Constraint.

columnname is the column on which you want to create your Check Constraint.

condition is the condition that must be met to succeed Check Constraint.

Example:

ALTER TABLE Employee
ADD CONSTRAINT chkSalaryConstraint CHECK (Salary<50000)

How to drop a Check Constraint:

The Syntax for dropping a Check Constraint is:

ALTER TABLE tablename

DROP CONSTRAINT constraintanme;

Where;

tablename specifies the name of the table.

constraintname specifies the name of Check Constraint.

Example:

ALTER TABLE Employee

DROP CONSTRAINT chkSalaryConstraint;

How to disable Check Constraint:

Syntax for disabling Check Constraint in SQL Server.

ALTER TABLE tablename
NOCHECK CONSTRAINT constraintname;

Where;

tablename specifies the name of the table.

constraintname specifies the name of Check Constraint.

Example:

ALTER TABLE Employee
NOCHECK CONSTRAINT gender;

How to enable Check Constraint:

The syntax for disabling Check Constraint in SQL Server.

ALTER TABLE tablename
CHECK CONSTRAINT constraintname;

Where;

tablename specifies the name of the table.

constraintname specifies the name of Check Constraint.

Example:

ALTER TABLE Employee
CHECK CONSTRAINT gender;

Conclusion:

Hope you understand how to create your own rule for the column in  SQL Server. I will appreciate, feedback and Comments.

View More:

• Primary Key in SQL Server.
• Foreign Key in SQL Server.
• Constraints in SQL Server.

Thank You.