SQL Keyword CHECK

The keyword CHECK is a constraint, which is used to limit the values that are allowed in a column.

  • It can be defined on a new table while creating it using the CREATE TABLE statement.
  • It can also be defined on an existing table using the ALTER TABLE statement.

CREATE TABLE With CHECK Constraint

The syntax between the databases has some minor differences, which can be observed from the below examples.

  • MySQL Server has a comma between the column definition and the CHECK constraint.
  • SQL Server, Oracle, or MS Access, doesn't have a comma between the column definition and the CHECK constraint.

CHECK Constraint defined using a single column on MySQL.

Run this on IDE

CREATE TABLE users (
    age int, CHECK (age >=18)
);

CHECK constraint defined using a single column on SQL Server, Oracle, or MS Access.

CREATE TABLE users (
    age int CHECK (age>=18)
);

CHECK constraint defined using multiple columns on any of these database servers, using CONSTRAINT keyword.

CREATE TABLE users (
    age int,
    city varchar(255),
    CONSTRAINT CHK_userAge CHECK (age>=18 AND city='London')
);

ALTER TABLE With CHECK Constraint

The syntax is the same across the database systems, like MySQL, SQL Server, Oracle, and MS Access.

CHECK Constraint defined using a single column on MySQL.

ALTER TABLE users
ADD CHECK (age>=18);

CHECK constraint defined using multiple columns, using CONSTRAINT keyword.

ALTER TABLE users
ADD CONSTRAINT CHK_userAge CHECK (age>=18 AND city='London');

DROP CHECK Constraint

The syntax between the database systems has some minor differences, which can be observed from the below examples.

We can easily drop an already defined CHECK constraint from a table using the below SQLs.

On MySQL, use the below command.

ALTER TABLE users
DROP CHECK CHK_userAge;

On SQL Server, Oracle, or MS Access, use the below command.

ALTER TABLE users
DROP CONSTRAINT CHK_userAge;

Related Links