CHECK CONSTRAINT

            Using check constraint we can restrict the unwanted data insertion into a column.

Applying constraint on a new table:

Syntax:

CREATE TABLE <Table name>

(

<Col 1> <Data type>,

<Col 2> <Data type>,

.

.

<Col n> <Data type>,

CONSTRAINT <Constraint name> <Constraint type> (<Column name> <Required condition>)

)

Example:

EMP table (Shown below) is having salary column (Sal), I want to restrict the data insertion if salary is less than 5000.

Table creation:

CREATE TABLE CheckCon_Tbl1

(

ID INT

,FirstName varchar (30)

,LastName Varchar(30)

,Gender Char(1)

,Dept INT

,Sal DECIMAL(18,4)

,CONSTRAINT Sal_CHK CHECK (Sal > 5000)

)

Data insertion:

If we try to insert a record with salary less than 5000, error message will popup.

INSERT INTO CheckCon_Tbl VALUES (101,'A','a','M',10,4000)

Error message:

The INSERT statement conflicted with the CHECK constraint "Sal_CHK". The conflict occurred in database "Test", table "dbo.CheckCon_Tbl", column 'Sal'.

The statement has been terminated.

Applying constraint on existing table:

            If we need to apply check constraint on existing table, the column data should satisfy the condition.

Syntax:

ALTER TABLE <Table name> ADD CONSTRAINT <Constraint name> <Constraint type> (<Column name> <Required condition>)

Example:

Table creation:

CREATE TABLE CheckCon_Tbl2

(

ID INT

,FirstName varchar (30)

,LastName Varchar(30)

,Gender Char(1)

,Dept INT

,Sal DECIMAL(18,4)

)

Adding constraint:

ALTER TABLE CheckCon_Tbl ADD CONSTRAINT Sal_CHK2 CHECK (Sal > 5000)

Drop constraint:

ALTER TABLE CheckCon_Tbl DROP CONSTRAINT Sal_CHK2