MySQL - CHECK

The MySQL CHECK constraint is used to ensures that all values in a column of a table satisfies specified condition. By defining a CHECK constraint on a table it limits the values that can be entered for this column. Using AND and OR operator, a CHECK constraint can constructed in such a way that can limit the values in certain columns based on values in other columns in the row.

MySQL CHECK constraint with CREATE TABLE

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which CHECK constraint is applied on column Age.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT CHECK (Age >= 21),
  Salary DECIMAL(18,2)
);

Alternatively, it can also be created as mentioned below:

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  CHECK (Age >= 21)
);

To provide a name to CHECK constraint, and to define a CHECK constraint on multiple columns (say City and Age), the following statement can be used:

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  CONSTRAINT CHK_Employee CHECK(Age >= 21 AND City = 'London')
);

MySQL CHECK constraint with ALTER TABLE

In the above example, a table called Employee is created. To enforce CHECK constraint on Age column, the statement is given below:

ALTER TABLE Employee
ADD CHECK (Age >= 21);

To provide a name to CHECK constraint, and to define a CHECK constraint on multiple columns (say Age and City), the statement is given below:

ALTER TABLE Employee
ADD CONSTRAINT CHK_Employee 
CHECK (Age >= 21 AND City = 'London');

DROP CHECK constraint

To drop CHK_Employee CHECK constraint from table called Employee, the statement is given below:

ALTER TABLE Employee
DROP CONSTRAINT CHK_Employee;

OR

ALTER TABLE Employee
DROP CHECK CHK_Employee;