MySQL CHECK Constraint

The CHECK constraint in MySQL limits the values that can be stored in a column based on a specified condition. If a value does not satisfy the condition, MySQL rejects the insert or update operation. This helps maintain data accuracy directly at the database level.

Why Use CHECK?

Without a CHECK constraint, invalid data like a negative salary, an age of 200, or a discount greater than 100% can be inserted into a table. The CHECK constraint acts as a gatekeeper that allows only logically valid values.

Syntax

Column Level

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

Table Level with Named Constraint

CREATE TABLE table_name (
    column_name datatype,
    CONSTRAINT constraint_name CHECK (condition)
);

Example: Employee Table with CHECK

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL(10,2) CHECK (salary > 0),
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O'))
);

Rules applied:

• age must be between 18 and 65
• salary must be greater than 0
• gender must be M, F, or O (Other)

Valid Insert

INSERT INTO employees VALUES (1, 'Kavya Nair', 30, 55000.00, 'F');

Invalid Insert (Age Out of Range)

INSERT INTO employees VALUES (2, 'Raj Patel', 15, 30000.00, 'M');

MySQL returns:

ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.

CHECK with Multiple Conditions

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(8,2),
    discount DECIMAL(5,2),
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount >= 0 AND discount <= 100)
);

Adding CHECK to an Existing Table

ALTER TABLE employees
ADD CONSTRAINT chk_age CHECK (age >= 18 AND age <= 65);

Removing a CHECK Constraint

ALTER TABLE employees
DROP CHECK chk_age;

CHECK Constraint Support in MySQL

The CHECK constraint was fully enforced starting from MySQL 8.0.16. In older versions, MySQL accepted the syntax but did not enforce the condition. Always use MySQL 8.0.16 or later to ensure CHECK constraints work correctly.

Viewing CHECK Constraints

SELECT CONSTRAINT_NAME, CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'your_database_name';

Key Points

• CHECK validates column values based on a logical condition.
• Any comparison or logical expression can be used as a condition.
• Named constraints are easier to manage and remove.
• MySQL enforces CHECK constraints starting from version 8.0.16.
• Multiple CHECK constraints can be applied to a single table.