Check Constraint in SQL Server

Why use Check Constraint?

Let's say, we have an integer AGE column, in a table. The AGE in general cannot be less than ZERO and at the same time cannot be greater than 150. But, since AGE is an integer column it can accept negative values and values much greater than 150.lets check with SQL server

alter table employee1 add age Int

select * from employee1

insert into employee1 values(55,'Ram','Jadhav','Pune','HR',90000,-22)

insert into employee1 values(55,'Ram','Jadhav','Pune','HR',90000,300)

So, to limit the values that can be added, we can use CHECK Constraint. 

Check Constraint

• Used to set the limit on column values. 

• Particular values are allowed to be inserted in the column.

• In Check Constraint we define logical condition when the condition is TRUE then it will be allowed to insert the records in the table.

Here We first drop the already available employee1 table and create a new table.

1. By using Create Command

We are able to add column level and table level check constraints.

At the time of creating table we providing three check constraint

1. Name Should be greater than 10 character (Table level)

2. Salary is greater than 0 (Column level)

3. age is greater than 0 and less than 150 (Table level)

create table employee1(EID Int Primary Key,Name varchar(50),Loc varchar(50),Dept varchar(50),salary decimal not null check(salary > 0),

age Int,

check(len(name) > 10),

constraint check_employee1_age check(age > 0 and age < 150)

)

insert into employee1 values(1,'Chaitanya','Pune','HR',10000,5)//The INSERT statement conflicted with the CHECK constraint "CK__employee1__Name__2180FB33". The conflict occurred in database "pratikshadb", table "dbo.employee1", column 'Name'.

insert into employee1 values(1,'Chaitanya Dattatrya Patil','Pune','HR',0,5)//The INSERT statement conflicted with the CHECK constraint "CK__employee1__salar__208CD6FA". The conflict occurred in database "pratikshadb", table "dbo.employee1", column 'salary'.

insert into employee1 values(1,'Chaitanya Dattatrya Patil','Pune','HR',10000,200)//The INSERT statement conflicted with the CHECK constraint "check_employee1_age". The conflict occurred in database "pratikshadb", table "dbo.employee1", column 'age'.

If we check the age column is nullable and if we provide  null value to age at the time insert the record. it is allowed to insert null the reason is the boolean expression evaluates to UNKNOWN, and allows the value.

insert into employee1 values(1,'Chaitanya Dattatrya Patil','Pune','HR',10000,null)

select * from employee1

2. By using Alter Command

alter table employee1 add constraint Check_employee1_Dept check(len(Dept) > 1)

insert into employee1 values(2,'Sangita Patil','Mumbai','',10000,33)//The INSERT statement conflicted with the CHECK constraint "Check_employee1_Dept". The conflict occurred in database "pratikshadb", table "dbo.employee1", column 'Dept'.

insert into employee1 values(2,'Sangita Patil','Mumbai','H',10000,33)//The INSERT statement conflicted with the CHECK constraint "Check_employee1_Dept". The conflict occurred in database "pratikshadb", table "dbo.employee1", column 'Dept'.

insert into employee1 values(2,'Sangita Patil','Mumbai','HR',10000,33)

select * from employee1

Drop Check Constraint

alter table employee1 drop constraint Check_employee1_Dept

insert into employee1 values(3,'Rajesh Jadhav','Pune','H',10000,33) // allowed to insert Dept value after removing constraint

select * from employee1