SQL CHECK

In this tutorial, we will learn how to use CHECK CONSTRAINT using SQL.

SQL CHECK Constraint

SQL CHECK constraint ensures that all the values in a column satisfied the certain conditions.

Once CHECK constraint is defined, a new record is inserted if specific values satisfy the CHECK constraint condition. And updating an existing record if specific values satisfy the CHECK constraint condition.

SQL CHECK on CREATE TABLE

To add a CHECK constraint when a new table is created, a statement is as follow:

SQL Server / Oracle / MySQL

CREATE TABLE Employee (
    ID int NOT NULL,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int,
    CONSTRAINT CHK_Person CHECK (Age>=18)
);

SQL Server / Oracle

CREATE TABLE Employee (
    ID int NOT NULL,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int CHECK (Age>=18)
);

MySQL

CREATE TABLE Employee (
    ID int NOT NULL,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int,
    CHECK (Age>=18)
);

SQL CHECK on ALTER TABLE Statement

To add a CHECK constraint after creating a table,

SQL Server / MySQL / ORACLE

ALTER TABLE Employee
ADD CHECK (Age>=18);

ALTER TABLE Employee
ADD CONSTRAINT CHK_EmpAge CHECK (Age>=18);

SQL DROP CHECK Constraint

To drop a CHECK constraint, a statement is as follow:

SQL Server / Oracle

ALTER TABLE Employee
DROP CONSTRAINT chk_constraintName;

ALTER TABLE Employee
DROP CONSTRAINT CHK_EmpAge;

MySQL

ALTER TABLE Employee
DROP CHECK CHK_EmpAge;