A CHECK constraint says that a condition must be true for every row. It is the most flexible constraint: you write the rule.
MySQL parsed but ignored CHECK constraints until version 8.0.16. If you run an older server (or MariaDB before 10.2.1), your checks do nothing. From 8.0.16, they are enforced.
CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
price DECIMAL(8,2) NOT NULL,
discount_price DECIMAL(8,2) NULL,
stock INT NOT NULL DEFAULT 0,
rating TINYINT UNSIGNED NULL,
CONSTRAINT chk_price_positive CHECK (price > 0),
CONSTRAINT chk_stock CHECK (stock >= 0),
CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5),
CONSTRAINT chk_discount CHECK (discount_price IS NULL OR discount_price < price)
);
INSERT INTO products (name, price, discount_price, rating) VALUES ('Notebook', 80, 70, 4);The rules in action
INSERT INTO products (name, price) VALUES ('Free thing', 0);ERROR 3819 (HY000): Check constraint 'chk_price_positive' is violated.INSERT INTO products (name, price, discount_price) VALUES ('Bad sale', 50, 60);ERROR 3819 (HY000): Check constraint 'chk_discount' is violated.UPDATE products SET stock = stock - 100 WHERE name = 'Notebook';ERROR 3819 (HY000): Check constraint 'chk_stock' is violated.The same constraint guards inserts and updates. The update that would make the stock negative is refused (an extra safety net for the atomic stock update in Updating Under Concurrency).
Column checks and table checks
- A check written next to one column may refer only to that column.
- A check written as a separate table-level constraint (as above) may refer to several columns of the same row:
discount_price < price.
CREATE TABLE events (
id INT AUTO_INCREMENT PRIMARY KEY,
starts_at DATETIME NOT NULL,
ends_at DATETIME NOT NULL,
email VARCHAR(100) NOT NULL CHECK (email LIKE '%_@_%.__%'),
CHECK (ends_at > starts_at)
);
INSERT INTO events (starts_at, ends_at, email) VALUES ('2026-08-01 10:00:00', '2026-08-01 09:00:00', 'a@example.com');ERROR 3819 (HY000): Check constraint 'events_chk_2' is violated.NULL and CHECK
A check passes when the condition is true or unknown (NULL). That is the SQL standard. CHECK (rating BETWEEN 1 AND 5) accepts NULL, since NULL BETWEEN 1 AND 5 is unknown. To forbid NULL, also declare NOT NULL:
INSERT INTO products (name, price, rating) VALUES ('Unrated', 10, NULL);
SELECT name, rating FROM products ORDER BY id;+----------+--------+
| name | rating |
+----------+--------+
| Notebook | 4 |
| Unrated | NULL |
+----------+--------+
2 rows in setWhat a check can contain
Allowed: columns of the same row, literals, operators, and deterministic built-in functions (CHAR_LENGTH, LOWER, ABS, REGEXP_LIKE…).
Not allowed:
- Subqueries, stored functions, user variables.
- Non-deterministic functions such as
RAND(),NOW(),UUID(). - Columns with
AUTO_INCREMENT, and references to other rows or tables.
CREATE TABLE bad_check (v INT, CHECK (v > RAND()));ERROR 3814 (HY000): An expression of a check constraint 'bad_check_chk_1' contains disallowed function: rand.If a rule needs another table (“the end date must not be after the contract’s end date”), use a foreign key, a trigger, or application logic.
Practical checks
| Rule | Check |
|---|---|
| Non-negative | CHECK (amount >= 0) |
| Percentage | CHECK (percent BETWEEN 0 AND 100) |
| Allowed list (small and stable) | CHECK (status IN ('new', 'paid', 'shipped')) |
| Not blank | CHECK (CHAR_LENGTH(TRIM(name)) > 0) |
| Date order | CHECK (end_date >= start_date) |
| Format | CHECK (REGEXP_LIKE(code, '^[A-Z]{3}-[0-9]{4}$')) |
| Exactly one of two columns set | CHECK ((a IS NULL) <> (b IS NULL)) |
| Positive only when a flag is set | CHECK (NOT is_paid OR paid_at IS NOT NULL) |
CREATE TABLE vouchers (code VARCHAR(8) NOT NULL, CONSTRAINT chk_code_format CHECK (REGEXP_LIKE(code, '^[A-Z]{3}-[0-9]{4}$')));
INSERT INTO vouchers VALUES ('ABC-1234');
INSERT INTO vouchers VALUES ('abc-12');ERROR 3819 (HY000): Check constraint 'chk_code_format' is violated.Adding a check to an existing table
MySQL checks all existing rows when you add the constraint. It fails if any row violates it:
CREATE TABLE staff (id INT PRIMARY KEY, salary INT);
INSERT INTO staff VALUES (1, 50000), (2, -5);
ALTER TABLE staff ADD CONSTRAINT chk_salary CHECK (salary >= 0);ERROR 3819 (HY000): Check constraint 'chk_salary' is violated.Fix the data and add it again. For a huge table, you can add the constraint as not enforced first, and enforce it later:
UPDATE staff SET salary = 0 WHERE salary < 0;
ALTER TABLE staff ADD CONSTRAINT chk_salary CHECK (salary >= 0) NOT ENFORCED;
ALTER TABLE staff ALTER CHECK chk_salary ENFORCED;
SELECT constraint_name, enforced FROM information_schema.check_constraints c JOIN information_schema.table_constraints t USING (constraint_schema, constraint_name) WHERE t.table_name = 'staff';+-----------------+----------+
| CONSTRAINT_NAME | ENFORCED |
+-----------------+----------+
| chk_salary | YES |
+-----------------+----------+
1 row in setNOT ENFORCED stores the rule without checking it, so new bad data can still enter until you enforce it. It also documents the intention.
Drop a check
ALTER TABLE staff DROP CHECK chk_salary;(DROP CONSTRAINT chk_salary also works in MySQL 8.0.19 and later.)
Check or ENUM or lookup table?
For a list of allowed values: CHECK (status IN (...)) is simple and portable. ENUM stores it compactly (ENUM). A lookup table with a foreign key is best when the list changes often.