Check Constraint in existing data.

Few days back I was trying to add a check constraint in a table, but the condition was such like that I do not have to worry about existing data, what I need to do was to restrict the new data with the valid constraints.

• Let’s create a table and insert few records.

Create Table Staff( id int, age int)
Insert into Staff values(1,20),(2,18),(3,21)

• Add a constraint to restrict age of  staff.

Alter table Staff add  Constraint staff_age Check (age>25);

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint “staff_age”. The conflict occurred in database “Rnd”, table “dbo.Staff”, column ‘age’.

• Add a constraint to restrict age of  staff with nocheck.

Alter table Staff with nocheck add  Constraint staff_age Check (age>25);

• Insert few more records

Insert into Staff values(4,26)
Insert into Staff values(5,22)

This last statement will generate following error.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint “staff_age”. The conflict occurred in database “Rnd”, table “dbo.Staff”, column ‘age’.
The statement has been terminated

• Validate the result

select * from staff

The following is output

Thanks

Manish