SQL-Check Constraint

In this tutorial, you will learn how to use the SQL Server CHECK constraint is used to to limit the value of a column when inserting or updating.

CHECK Constraint

Check constraints are the user defined business rules which can be applied to the database table column. The CHECK constraint is used to limit the value of a column when inserting or updating. Check Constraint can be defined as the column level or the table level. If you define a CHECK constraint on a column it will allow only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row. The Check constraints must be a Boolean expression that can be evaluated using the values in the rows being inserted or updated.

The following CREATE TABLE statement defines a check constraint on the Salary column of the Employee table. you can use the CONSTRAINT keyword as follows:

CREATE TABLE Employee (
Eid int NOT NULL,
FirstName varchar(100),
LastName varchar(100),
Emailid varchar(100),
Age int,
Salary int,
Departmentid int,
CHECK(Salary > 5000 AND Salary < 10000))

T able where values must be evaluated to True for a boolean expression Salary > 5000 AND Salary < 10000 .

Output

Now insert data in parent table - Employee

Insert into Employee( Eid,FirstName, LastName, Emailid, Age, Salary, Departmentid) 
values(1, 'Rohatash', 'Kumar', 'Rohatash.mca@gmail.com', 20, 4000, 1)

Output

CHECK Constraint on Multiple Columns

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Employee (
Eid int NOT NULL,
FirstName varchar(100),
LastName varchar(100),
Emailid varchar(100),
Age int,
Salary int,
Departmentid int,
CHECK(Salary > 5000 AND age < 100)
);

Now insert data in table - Employee. We try to insert age 101 which is greated than 100. So it will not insert and show a message.

Insert into Employee(Eid,FirstName,LastName,Emailid,Age,Salary, Departmentid) 
values(1, 'Rohatash', 'Kumar', 'Rohatash.mca@gmail.com',101, 6000, 1 )

Adding CHECK Constraint to Existing Columns

To add the CHECK constraint to an existing column, The Employee table is already created, use the following SQL Query:

ALTER TABLE Employee 
ADD CHECK (Age>=100);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Employee 
ADD CONSTRAINT CHK_empsalaryAge CHECK (Age>=100 AND salary>5000);

Removing CHECK Constraint

To remove the CHECK constraint from a column, you can use the ALTER TABLE ALTER COLUMN statement as follows:

ALTER TABLE Employee 
DROP CONSTRAINT CHK_empsalaryAge;