SQL -Structured Query Language - Sql Key & Constraint - Check Constraint Tutorial

CHECK Constraint is used, when we want to limit the value range that is going to be inserted in a column.

For example, Suppose we want that the data of employee should be inserted whose age is greater than 20.

Then the CHECK constraint will check, whether the age is greater than 20 or not. If not, then the data will not be inserted.

The syntax for Check Constraint-

create table user_detail

(

    id int,

    first_name varchar(50),
  
    age int CHECK(age>20)

);

create table user_detail

(

    id int,

    first_name varchar(50),
  
    age int,
  
    CONSTRAINT CHK_age CHECK (age>20)

);

Run Query

In this, we define age with CHECK constraints with age is greater than 20 while creating a user_detail table. With a CHECK constraint, it doesn’t allow inserting an invalid record.

It will give an error (CHECK constraint failed: user_detail) while inserting the age less or equal to 20.

Syntax For Adding CHECK Constraint With Alter Command-

ALTER TABLE user_detail
ADD CHECK (age>20);

ALTER TABLE user_detail
ADD CONSTRAINT CHK_age CHECK (age>20);

This syntax will add a CHECK Constraint in age after table creation. If it gives an error make sure that records should not contain an invalid value other than the defined condition.

Syntax For Dropping Check Constraint With Alter Command-

ALTER TABLE user_detail
DROP CHECK CHK_age;

This syntax will drop a CHECK Constraint with its constraint name.