MySQL CHECK
MySQL CHECK Constraint
The value range that can be entered into a column is restricted by the CHECK constraint.
A column that has a CHECK constraint defined on it will only accept specific values.
A table’s CHECK constraint might restrict values in specific columns according to values in other columns in the row.
CHECK on CREATE TABLE
Upon creating the “Persons” table, the following SQL places a CHECK constraint on the “Age” field. The CHECK constraint guarantees that an individual must be eighteen years of age or older:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
Use the following SQL syntax to define a CHECK constraint on multiple columns and to allow naming of the constraint:
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
When the table has already been established, use the following SQL to create a CHECK constraint on the “Age” column:
ALTER TABLE Persons
ADD CHECK (Age>=18);
Use the SQL syntax below to define a CHECK constraint on several columns and to permit naming of a CHECK constraint:
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
DROP a CHECK Constraint
To remove a CHECK constraint, use the SQL code below:
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
