CHECK constraint is used to limit the value range on table in SQL. CHECK constraint is defined with a condition. Whenever we are trying to insert rows, the value for the column with CHECK constraint should satisfy the condition in CHECK constraint. If the value satisfies, the row gets inserted. Otherwise, row insertion gets failed.

We can define CHECK constraint in two ways. Those are –

• Using CREATE TABLE Statement - While creating new table.
• Using ALTER TABLE Statement - Through altering existing table.

CREATE TABLE ( 
     column1 datatype [CHECK (condition)],
     column2 datatype [CHECK (condition)],
     …,    
     columnN datatype [CHECK (condition)],
 [CONSTRAINT constraint_name CHECK (condition)]); 

CREATE TABLE ( 
     column1 datatype [CHECK (condition)],
     column2 datatype [CHECK (condition)],
        …,    
     columnN datatype [CHECK (condition)],
  [CONSTRAINT constraint_name CHECK (Condition1 [AND|OR] Condition2)]); 

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (Condition); 

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name CHECK (Condition1 [AND|OR] Condition2); 

ALTER TABLE table_name
DROP CONSTRAINT constraint_name; 

• table_name – Represents on which table user want to create CHECK constraint.
• column_name - Represents on which column included in CHECK constraint.
• constraint_name - Represents user defined name of the CHECK constraint.

Scenario – Creating CHECK constraint while creating table.

Requirement – Creating CHECK constraint on dept_id while creating employee_details table. The query was as follows -

CREATE TABLE employee_details ( 
     emp_id INT NOT NULL,
     emp_name VARCHAR (20) NOT NULL,                               
     dept_id INT CHECK (dept_id = 1000)); 

By executing above query, we can create CHECK constraint on employee_details table with condition dept_id should be 1000.

Scenario – Creating CHECK constraint while creating table with multiple columns.

Requirement – Creating CHECK constraint on dept_id while creating employee_details table. The query was as follows -

CREATE TABLE employee_details (
     emp_id INT NOT NULL,
     emp_name VARCHAR (20) NOT NULL,
     dept_id INT CHECK (dept_id = 1000 OR dept_id = 2000)); 

By executing above query, we can create CHECK constraint on employee_details table with condition dept_id should be 1000 or 2000.

Scenario – Creating CHECK constraint on existing table.

Requirement – Creating dept_id as CHECK constraint on existing employee_details table which does not have a CHECK constraint. The query was as follows -

ALTER TABLE employee_details
  ADD CONSTRAINT ck_dept_id
  CHECK  (dept_id = 1000);   

By executing above query, ch_dept_id was created as CHECK constraint on employee_details table.

Scenario – Creating CHECK constraint on existing table with multiple conditions.

Requirement – Creating dept_id as CHECK constraint on existing employee_details table with multiple conditions. The query was as follows -

ALTER TABLE employee_details
  ADD CONSTRAINT ck_dept_id
  CHECK  (dept_id = 1000 OR dept_id = 2000);   

By executing above query, emp_id was created as CHECK constraint on employee_details table with multiple conditions.

Scenario – Deleting CHECK constraint on existing table.

Requirement – Deleting ck_dept_id CHECK constraint on existing employee_details table. The query was as follows -

 ALTER TABLE employee_details DROP CONSTRAINT ck_dept_id;   

By executing above query, ck_dept_id CHECK constraint gets deleted on employee_details table.