How To Use CHECK Constraint In SQL

In this article, we will learn how to use the CHECK constraint in SQL.

To limit the value range that can be placed in a column, the CHECK constraint is used.

If we are defining a CHECK constraint on a column it allows only certain values for this column based on condition.

If we are defining a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

 

CHECK – CREATE TABLE:

Please check How To Create Table Using Query In SQL, to get a brief description of the CREATE TABLE statement.

Example-1

The subsequent statement would create a CHECK constraint on the “Age” column when the “User” table is created, to ensure that you can not have any user below 10 years:

CREATE TABLE User (
    ID int,
    Name varchar(100),
    City varchar(100),
    Age int CHECK (Age>=10)
);

Example-2

The subsequent statement would create a CHECK constraint on the “Age” and “City” columns when the “User” table is created:

CREATE TABLE User (
    ID int,
    Name varchar(100),
    City varchar(100),
    Age int,
    CONSTRAINT CHKUser CHECK (Age>=10 AND City='CA')
);

 

CHECK – ALTER TABLE:

Please check How To Alter Table Using Query In SQL, to get a brief description of the ALTER TABLE statement.

• To ADD a CHECK Constraint

Syntax

ALTER TABLE TABLE_NAME 
ADD CONSTRAINT CONSTRAINT_NAME CHECK (condition1 AND condition2 AND ...conditionN);

Example

The subsequent statement would add a CHECK constraint on the “Age” and “City” columns using ALTER TABLE statement:

ALTER TABLE User 
ADD CONSTRAINT CHKUser CHECK (Age>=18 AND City='CA');

• To DROP a CHECK Constraint

Syntax

ALTER TABLE TABLE_NAME 
DROP CONSTRAINT CONSTRAINT_NAME;

Example

The subsequent statement would delete a CHECK constraint named “CHKUser” using ALTER TABLE statement:

ALTER TABLE User 
DROP CONSTRAINT CHKUser;

 

Also, check How To Use FOREIGN KEY Constraint In SQL