CHECK Constraint MySQL

A CHECK constraint is used to control or limit the value of a particular column of the table. Every time CHECK constraint ensures to match a particular condition of inserted value.

Before MySQL version 8..0.16, We can use the CHECK constraint but it was parsed or ignored by MySQL In the previous version we can use it by using WITH CHECK OPTION or TRIGGER. After version 8.0.16 all MySQL storage engines support CHECK constraints.

CHECK constraints basically two types:-

A table CHECK constraint applies multiple columns of the table.

A column CHECK constraint applies only to a single column of the particular table.

Syntax of CHECK constraint:-

[CONSTRAINT [constraint_name]] CHECK (expression) [[NOT] ENFORCED]

First, we define constraint_name, If we do have not a defined constraint name then MySQL automatically generates a constraint name.

the expression will be boolean.

If we want to enforce the clause, then ENFORCE statement will use. If we do not want to enforce the clause the NOT ENFORCE statement will use.

Example:-

Single column CHECK constraint:-

CREATE TABLE employee(

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

CHECK (Age>=18)

);

Multiple column CHECK constraint:-

CREATE TABLE employee (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

Department varchar(255),

CONSTRAINT CHK_Employee CHECK (Age>=18 AND Department!=’Admin’)

);

If the table is already created and then we want to use the CHECK constraint then the syntax will be:-

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

For multiple columns:-

ALTER TABLE employee ADD CONSTRAINT CHK_Employee  CHECK (Age>= 18 AND Department!=’ Admin’);

If we want to delete or drop the CHECK constraint from the table, then the syntax will be:-

ALTER TABLE employee DROP CHECK CHK_Employee;

Read Also:-

PRIMARY KEY CONSTRAINT
FOREIGN KEY CONSTRAINT
UNIQUE CONSTRAINT
NOT NULL CONSTRAINT
DEFAULT CONSTRAINT
CREATE INDEX CONSTRAINT
AUTO_INCREMENT CONSTRAINT
INDEX CONSTRAINT
ENUM CONSTRAINT

For more detail about MySQL constraints please follow here