MySQL CHECK

MySQL CHECK Constraint

The CHECK constraint restricts the range of values that can be inserted into a column.

When applied to a column, check permits only specific values for that column.

When applied to a table, it can restrict values in certain columns based on the values in other columns within the same row.

CHECK on CREATE TABLE

The following SQL statement establishes a CHECK constraint on the “Age” column during the creation of the “Persons” table. This constraint ensures that a person’s age must be 18 or older:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

To assign a name to a CHECK constraint and define it for multiple columns, use the following SQL syntax:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City=‘Sandnes’)
);

CHECK on ALTER TABLE

To add a CHECK constraint to the “Age” column in an existing table, use the following SQL:

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

To name a CHECK constraint and define it for multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City=‘Sandnes’);

DROP a CHECK Constraint

To remove a CHECK constraint, use the following SQL:

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;