;

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

Example - CHECK on CREATE TABLE - 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

Example - CHECK on CREATE TABLE - SQL Server / Oracle
CREATE TABLE Employee (
    ID int NOT NULL,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int CHECK (Age>=18)
);

MySQL

Example - CHECK on CREATE TABLE - 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

Example - CHECK on ALTER TABLE Statement - SQL Server / MySQL / ORACLE
ALTER TABLE Employee
ADD CHECK (Age>=18);
Example
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

Syntax - DROP CHECK Constraint - SQL Server / Oracle
ALTER TABLE Employee
DROP CONSTRAINT chk_constraintName;
Example - DROP CHECK Constraint - SQL Server / Oracle
ALTER TABLE Employee
DROP CONSTRAINT CHK_EmpAge;

MySQL

Example - DROP CHECK Constraint - MySQL
ALTER TABLE Employee
DROP CHECK CHK_EmpAge;