MySQL CHECK Constraint

The CHECK constraint lets you define a custom rule that every value stored in a column must satisfy. If an INSERT or UPDATE operation produces a value that fails the check condition, MySQL rejects the operation and returns an error.

For example, you can use CHECK to ensure that an age column only accepts positive numbers, that a price is never zero or negative, or that a rating falls within a specific range like 1 to 5.

---

MySQL Version Note

---

Syntax

You can define a CHECK constraint inline on a column or at the table level:

-- Inline on a column
column_name data_type CHECK (condition)

-- Table-level with an optional name
CONSTRAINT constraint_name CHECK (condition)

---

Inline CHECK Constraint

The following example ensures that the age column only accepts values of 18 or above:

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

If you try to insert an employee with an age below 18 or a salary of zero, MySQL rejects it:

INSERT INTO employees (name, age, salary) VALUES ('Rafi', 15, 25000.00);
-- ERROR 3819 (HY000): Check constraint 'employees_chk_1' is violated.

---

Named CHECK Constraint

Giving a constraint a name makes the error message clearer and makes the constraint easier to drop later:

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

Now when the rule is violated, the error message will include the name chk_age or chk_salary, which makes debugging much faster.

---

Multi-Column CHECK Constraint

A table-level CHECK constraint can reference multiple columns in the same expression. For example, ensure that the end_date is always later than the start_date:

CREATE TABLE projects (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(150) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT chk_dates CHECK (end_date > start_date)
);

---

CHECK with IN — Restricting Allowed Values

You can use IN inside a CHECK to limit a column to a fixed set of allowed values — similar to an enum:

CREATE TABLE orders (
    id INT NOT NULL AUTO_INCREMENT,
    status VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT chk_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

---

Adding CHECK to an Existing Table

Use ALTER TABLE to add a CHECK constraint to a table that already exists:

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

---

Dropping a CHECK Constraint

To remove a named CHECK constraint, use ALTER TABLE ... DROP CHECK:

ALTER TABLE employees
DROP CHECK chk_age;

---

Key Points to Remember

• CHECK enforces a custom condition on column values — any insert or update that violates the condition is rejected.
• Full enforcement requires MySQL 8.0.16 or later — older versions parse the syntax but do not enforce it.
• Always name your CHECK constraints to get clearer error messages and easier management.
• A table-level CHECK can reference multiple columns in a single expression.
• Use CHECK ... IN (...) to restrict a column to a specific list of acceptable values.
• When adding CHECK to an existing table, all current rows are validated — any failures will block the operation.