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

Footer Logo

Subscribe

Select Categories