CHECK Constraint in MySQL

The CHECK constraint in MySQL allows you to define a condition that data must satisfy before it can be inserted into or updated in a table.

For example, suppose you have a student’s age field defined in a CREATE TABLE statement. You can add CHECK (Age > 0) to ensure that only values greater than zero can be stored in the age field. If a user attempts to insert or update a row with an age value less than or equal to zero, MySQL rejects the operation.

A CHECK constraint evaluates a Boolean expression. If the expression evaluates to TRUE, the operation succeeds. If it evaluates to FALSE, MySQL rejects the entire INSERT or UPDATE operation and raises an error.

The CHECK constraint in MySQL helps maintain data accuracy and consistency by restricting the values allowed in one or more columns. MySQL began enforcing CHECK constraints starting with MySQL 8.0.16.

Syntax of MySQL CHECK Constraint

In MySQL, we can define a CHECK constraint in two ways:

• Column-Level CHECK Constraint
• Table-Level CHECK Constraint

Both types are used to enforce validation rules on the data stored in a table. Let understand them one by one with syntax and examples.

1. Column-Level CHECK Constraint

A column-level CHECK constraint is defined immediately after a column definition. We usually use it when we have to apply the validation rule to a single column. The general syntax to define CHECK constraint with a column is:

CREATE TABLE table_name (
    column_name data_type CHECK (condition)
);

In the above syntax:

• The CREATE TABLE statement creates a new table in the database.
• The column_name specifies the name of the column.
• The datatype defines the data type of the column.
• The constraint CHECK specifies the validation rule applied to the column.
• A condition is a Boolean expression that the column value must satisfy.
• If the condition evaluates to TRUE, MySQL accepts the data.
• If the condition evaluates to FALSE, MySQL rejects the INSERT or UPDATE operation.

Example 1: CHECK Constraint on Age

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT CHECK (Age > 0)
);

In this example:

• The Age column stores integer values.
• The CHECK constraint ensures that the age must be greater than 0.
• Values of age such as 1, 18, and 25 are allowed.
• Values of age such as 0 and -5 are rejected.

Insert Valid Data

INSERT INTO Students
VALUES (101, 'John', 20);

Output:

Record inserted successfully.

Attempting to Insert Invalid Data

INSERT INTO Students
VALUES (102, 'David', -5);

Output:

ERROR: Check constraint violated.

MySQL rejects the row because the age column violates the CHECK condition.

2. Table-Level CHECK Constraint

A table-level CHECK constraint is defined separately from the column definitions. It can reference one or more columns in the table. The general syntax to define the CHECK constraint with one or more columns in the table is:

CREATE TABLE table_name (
    column_name1 data_type1,
    column_name2 data_type2,
    CHECK (condition)
);

In this syntax:

• The CHECK constraint is declared at the table level.
• It can validate data based on multiple columns in the table.
• It is useful when you want to apply rules to more than one column.
• MySQL evaluates the condition whenever a row is inserted or updated in the database table.

Example 2: Constraint CHECK on Multiple Columns

CREATE TABLE Students (
    Age INT,
    Marks INT,
    CHECK (Age >= 18 AND Marks BETWEEN 0 AND 100)
);

In this example:

• There are two columns named Age and Marks, respectively.
• The Age column in the table must contain values greater than or equal to 18.
• The Marks column in the table must contain values between 0 and 100.
• Both conditions must be satisfied for the row to be inserted or updated successfully.
• If either one condition fails, MySQL rejects the operation.

Insert Valid Data

INSERT INTO Students
VALUES (20, 85);

Output:

Record inserted successfully.

Attempting to Insert Invalid Data

INSERT INTO Students
VALUES (16, 85);

Output:

ERROR: Check constraint violated.

MySQL rejects the record because the age is less than 18.

Adding CHECK Constraint to an Existing Table

You can add a CHECK constraint using ALTER TABLE statement to an existing table. The basic syntax to create a CHECK constraint on a single column is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (condition);

Example 3: Adding a CHECK Constraint on a Single Column in an Existing Table

Suppose you already have a Students table in the MySQL database:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50),
    Age INT
);

Now, you want to ensure that the Age column always contains a value greater than zero.

Add CHECK Constraint

ALTER TABLE Students
ADD CONSTRAINT chk_age
CHECK (Age > 0);

In this example code:

• ALTER TABLE Students modifies the existing table in the database.
• ADD CONSTRAINT chk_age creates a CHECK constraint named chk_age.
• CHECK (Age > 0) ensures that only positive age values are allowed.
• MySQL validates this condition whenever a row is inserted or updated.

Insert Valid Data

INSERT INTO Students
VALUES (101, 'John', 20);

Output:

Record inserted successfully.

Trying to Insert Invalid Data

INSERT INTO Students
VALUES (102, 'David', -5);

Output:

ERROR: Check constraint violated.

Example 4: Adding a CHECK Constraint on Multiple Columns in an Existing Table

Suppose you have an Employees table in the database:

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    Salary DECIMAL(10,2),
    Bonus DECIMAL(10,2)
);

Now, you want to enforce the rules that salary must be at least ₹10,000 and bonus cannot be greater than salary.

Add CHECK Constraint

ALTER TABLE Employees
ADD CONSTRAINT chk_salary_bonus
CHECK (Salary >= 10000 AND Bonus <= Salary);

In this example, the constraint uses both Salary and Bonus columns. Both conditions must be TRUE for the operation to succeed.

Insert Valid Data

INSERT INTO Employees
VALUES (101, 'Rahul', 50000, 10000);

Output:

Record inserted successfully.

Key Difference Between Column-Level and Table-Level CHECK Constraints

The key difference between column-level and table-level CHECK constraints in MySQL is as follows:

Dropping a CHECK Constraint in MySQL

To remove a CHECK constraint, use the following syntax:

ALTER TABLE table_name
DROP CHECK constraint_name;

Example 5:

ALTER TABLE Employees
DROP CHECK chk_salary;

Best Practices for Using CHECK Constraints

There are the following key points that you should keep in mind while using CHECK constraints in MySQL:

• Always use meaningful constraint names.
• Always try to keep conditions simple and readable.
• Use CHECK constraints for business rules.
• Combine with NOT NULL constraint when needed.