How to Define and Enforce CHECK Constraints in MySQL

This article provides a straightforward guide on how to define and enforce CHECK constraints in MySQL. You will learn the syntax for creating these constraints during table creation, how to add them to existing tables, and how MySQL handles their enforcement.

Understanding CHECK Constraints in MySQL

A CHECK constraint is a type of integrity constraint that limits the values that can be placed in a column or combination of columns. It evaluates a Boolean expression for each row; if the expression evaluates to false, MySQL rejects the insert or update operation with an error.

Important Version Note: While older versions of MySQL parsed the CHECK syntax, they silently ignored it. Full support and active enforcement of CHECK constraints were introduced in MySQL 8.0.16. Ensure you are using MySQL 8.0.16 or later for these constraints to be enforced.

Defining CHECK Constraints on New Tables

You can define CHECK constraints at either the column level or the table level when creating a new table.

Column-Level Constraints

A column-level constraint is declared directly next to the column definition. It is ideal for simple conditions that only involve a single column.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

Table-Level Constraints

A table-level constraint is defined after all columns are declared. Use this approach if you want to name the constraint or if the condition compares multiple columns.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    regular_price DECIMAL(10, 2),
    sale_price DECIMAL(10, 2),
    CONSTRAINT chk_sale_price CHECK (sale_price <= regular_price)
);

Adding CHECK Constraints to Existing Tables

To enforce a new rule on an already existing table, use the ALTER TABLE statement.

ALTER TABLE employees
ADD CONSTRAINT chk_name_length CHECK (CHAR_LENGTH(name) >= 2);

Note: If the existing data in your table violates the new constraint, the ALTER TABLE statement will fail with an error.

Enforcing and Disabling CHECK Constraints

By default, declared CHECK constraints are active and enforced. However, MySQL allows you to explicitly state whether a constraint should be enforced using the ENFORCED or NOT ENFORCED keywords.

Disabling a Constraint during Creation or Alteration

You can define a constraint but keep it inactive by appending NOT ENFORCED:

ALTER TABLE employees
ADD CONSTRAINT chk_max_salary CHECK (salary < 1000000) NOT ENFORCED;

Toggling Enforcement State

To enable or disable an existing constraint, use the ALTER TABLE command with the ALTER CONSTRAINT clause:

-- To enable the constraint
ALTER TABLE employees
ALTER CONSTRAINT chk_max_salary ENFORCED;

-- To disable the constraint
ALTER TABLE employees
ALTER CONSTRAINT chk_max_salary NOT ENFORCED;

Dropping CHECK Constraints

If a business rule changes and you no longer need a CHECK constraint, you can remove it using the DROP CONSTRAINT clause.

ALTER TABLE employees
DROP CONSTRAINT chk_name_length;