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
