MySQL CHECK Constraint

The CHECK constraint will check for certain value to be add to a column in a table. This constaint is used to provide restriction in adding values to the column(s) as per the need.

Example

   create table employee(empno int PRIMARY KEY, 
   name varchar(50) NOT NULL,
   age numeric, 
   role varchar(50) NOT NULL, 
   location varchar(50) NOT NULL, 
   salary decimal NOT NULL,
   CHECK (age>21)
   );

This will accept the age of the employee to be above 21.

Its also possible to add multiple check condition to the columns using the following syntax

   create table employee(empno int PRIMARY KEY, 
   name varchar(50) NOT NULL,
   age numeric, 
   role varchar(50) NOT NULL, 
   location varchar(50) NOT NULL, 
   salary decimal NOT NULL,
   CONSTRAINT CHK_EMPLOYEE CHECK(age>21 and location='India')
   );

DROP CHECK Constraint

In order to drop any check condition we need to use DROP CHECK in the ALTER TABLE statement.

Syntax for DROP CHECK Constraint

alter table table_name
drop CONSTRAINT constraint_name;

Example

alter table employee
drop CONSTRAINT CHK_EMPLOYEE;

CHECK on ALTER TABLE

To create CHECK on any column we can use alter table as below query

alter table employee
add CHECK (age>21);

It is possible to add multiple CHECK constraint to the columns with ALTER TABLE.

alter table employee
CONSTRAINT CHK_EMPLOYEE CHECK(age>21 and location='India');