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 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 constraint 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 syntax will be:-
ALTER TABLE employee ADD CHECK (Age>= 18);
For multiple column:-
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 syntax will be:-
ALTER TABLE employee DROP CHECK CHK_Employee;