CHECK

What CHECK Does

A CHECK constraint enforces an arbitrary boolean condition on every row: any INSERT or UPDATE that would make the expression evaluate to FALSE is rejected. Rows where the expression evaluates to UNKNOWN (typically because a referenced column is NULL) are allowed — CHECK only blocks definite FALSE, not UNKNOWN.

Column-Level Syntax

sql
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    salary NUMERIC(10,2) CHECK (salary > 0),
    age    INT CHECK (age >= 18)
);

Table-Level Syntax (needed when the condition spans multiple columns)

sql
CREATE TABLE bookings (
    check_in  DATE,
    check_out DATE,
    CHECK (check_out > check_in)
);

CHECK (check_out > check_in) references two columns, so it must be written as a table-level clause; you cannot attach it inline to just one of the two columns.

Adding CHECK to an Existing Table

sql
ALTER TABLE employees ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);

As with other constraints, this validates every existing row; a salary of 0 or negative anywhere in the table causes the ALTER TABLE to fail.

The Historical MySQL Gotcha

For years — through MySQL 5.x and early 8.0 releases before 8.0.16 (released 2019) — MySQL's parser accepted CHECK constraint syntax without any error, but silently discarded it at execution time. You could declare CHECK (salary > 0), insert a row with salary = -500, and MySQL would happily accept it with no warning. This caught many developers off guard because the DDL looked perfectly valid and no error was ever raised — the constraint simply did nothing. MySQL 8.0.16 and later actually enforce `CHECK` constraints properly. This is a well-known, frequently-tested historical gotcha: always verify your MySQL version (or migrate to PostgreSQL, which has enforced CHECK correctly since its earliest versions) before relying on CHECK for real data integrity.

Edge Cases

• CHECK (col IS NOT NULL) is technically valid but redundant with — and less efficient than — a plain NOT NULL constraint; prefer NOT NULL for that specific case.
• A CHECK constraint can reference multiple columns but, in most engines, cannot reference another table (no subqueries) — that kind of cross-table rule needs a trigger instead.
• CHECK conditions involving NULL follow three-valued logic: CHECK (age >= 18) on a row where age IS NULL evaluates to UNKNOWN, not FALSE, so the row is accepted — a frequent source of confusion when people expect NULL to fail a numeric check.

Key Takeaways / Q&A

Q: Does a CHECK constraint reject a row when the expression evaluates to NULL/UNKNOWN? A: No — only a definite FALSE result blocks the row; UNKNOWN (from NULL operands) is treated as passing.

Q: Why is the "old MySQL silently ignores CHECK" issue significant? A: Because the DDL parses without error, developers could believe integrity was enforced when it wasn't — a real, historically documented pitfall (fixed in MySQL 8.0.16+).

Q: When must CHECK be written at table level rather than column level? A: Whenever the boolean expression involves more than one column, e.g. comparing check_out to check_in.