What is check constraint in SQL SERVER?

Check constraint is the constraint on the column where you can restrict the value in columns as per data type or some numeric value. But we can not apply check constrains on null value i.e. if you are inserting the null value in column that will get inserted in the column where you have put some check constraint.

·         For check constraint there should be at least one row in the table on which constrain can be specified.
·         Delete operation doesn’t s validates check constraint. 

1       Create Check Constraint:

CREATE TABLE AvadTemp
(
EMP_Id int NOT NULL CHECK (EMP_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255)

 ) 

2      To apply check constraint for multiple columns use syntax as: 

CREATE TABLE AvadTemp
(
EMP_Id int NOT NULL,
Name varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT chk CHECK (EMP_Id>0 AND City=’Delhi’)
)

3        Alter existing table to add Check Constraint:

ALTER TABLE AvadTemp
ADD CHECK (EMP_Id>0)

4       Delete Check Constraint from table:

ALTER TABLE AvadTemp
DROP  CONSTRAINT  chk