Check Constraint

 Constraint are rules enforced on column.

1. check constraint allows you to specify a condition on each row in a table.

2. We defined it on Table only not for View.

we can check data (value) before inserting data into the table.

1. while creating table.

Create Table Emp

(

EmpId Int Identity(1,1),

Name Varchar(50) Not Null,

Age Int check (Age>=20),

CreatedDate DateTime Default getdate()

)

2. while creating table using constraint

Create Table Emp

(

EmpId Int Identity(1,1),

Name Varchar(50) Not Null,

Age Int check (Age>=20),

CreatedDate DateTime 

Constraint const_check Check(CreatedDate<=getdate())

)

3. Add after table Creation -New Column 

Alter Table Emp

Add City varchar(50) Check (City<>'Delhi')

4. Add after table Creation -Existing Column

Alter Table Emp

Add Constraint Const_Check Check (Name<>'')

Disable Constraint

Alter Table Emp

NoCheck Constraint Const_Check

Note - We can disable foreign key constraint and Check Constraint.

Drop Constraint

Alter Table Emp

Drop Constraint Const_Check

Example

Create table Account

(

Id INT Primary Key,

Salary Int check(Salary>0)

)

Insert Into Account Values (1,2000)   --Allow

Insert Into Account Values (2,null)   --Allow

Insert Into Account Values (3,-5)   --Not Allow because -5 not greater than 0.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CK__Account__Salary__4E88ABD4". The conflict occurred in database "Practice", table "dbo.Account", column 'Salary'.

The statement has been terminated.

Solution:

Alter table Account

Nocheck Constraint CK__Account__Salary__4E88ABD4

or Condition like check(Salary>=-5)

Insert Into Account Values (3,-5) ---Possible.

Enable Constraint

Alter table Account

check Constraint CK__Account__Salary__4E88ABD4