00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL CHECK

Last updated: 5 min read

A CHECK constraint is a rule written as a condition, such as price must be positive or end date must be after start date. MySQL tests it for every inserted or updated row and rejects rows that break it. CHECK constraints are enforced since MySQL 8.0.16; older versions parsed them but ignored them.

1. Syntax

syntax.sql 
-- column level
column_name type CHECK (condition)

-- table level (can use several columns), optionally named
[CONSTRAINT name] CHECK (condition) [[NOT] ENFORCED]
A CHECK constraint tests each rowEach new or changed row is tested against the condition price > 0 AND discount <= price. If the result is TRUE or NULL the row is accepted; if it is FALSE the statement fails with error 3819.new / changed rowCHECK (price > 0AND discount <= price)TRUE or NULLrow acceptedFALSEERROR 3819
Figure 1: a row passes if the condition is TRUE or NULL; only FALSE is rejected.

2. A Product Table with Checks

create.sql 
CREATE TABLE products (
  sku       VARCHAR(10) PRIMARY KEY,
  name      VARCHAR(40) NOT NULL,
  price     DECIMAL(8,2) NOT NULL CHECK (price > 0),
  discount  DECIMAL(8,2) NOT NULL DEFAULT 0,
  stock     INT NOT NULL CHECK (stock >= 0),
  rating    DECIMAL(2,1) CHECK (rating BETWEEN 1 AND 5),
  CONSTRAINT chk_discount CHECK (discount >= 0 AND discount <= price)
);
INSERT INTO products VALUES ('BAG-1', 'Laptop Bag', 1800, 200, 25, 4.5);
violations.sql 
INSERT INTO products VALUES ('BAG-2', 'Sling Bag', -10, 0, 5, NULL);
INSERT INTO products VALUES ('BAG-3', 'Tote', 900, 1000, 5, NULL);
INSERT INTO products VALUES ('BAG-4', 'Pouch', 300, 0, -2, NULL);
INSERT INTO products VALUES ('BAG-5', 'Wallet', 650, 50, 10, 7);
UPDATE products SET stock = stock - 30 WHERE sku = 'BAG-1';
Output 
ERROR 3819 (HY000): Check constraint 'chk_discount' is violated.
ERROR 3819 (HY000): Check constraint 'chk_discount' is violated.
ERROR 3819 (HY000): Check constraint 'products_chk_2' is violated.
ERROR 3819 (HY000): Check constraint 'products_chk_3' is violated.
ERROR 3819 (HY000): Check constraint 'products_chk_2' is violated.
MySQL names unnamed checks automatically, like products_chk_1. Named constraints such as chk_discount make errors much easier to understand.

3. NULL Passes a CHECK

A CHECK fails only when its condition is FALSE. NULL BETWEEN 1 AND 5 is NULL, so a missing rating is accepted:

null-passes.sql 
INSERT INTO products VALUES ('BAG-6', 'Key Chain', 99, 0, 100, NULL);
SELECT sku, rating FROM products;

Result: 2 rows

skurating
BAG-14.5
BAG-6NULL

If the value must exist, also declare the column NOT NULL.

4. Rules Across Columns

multi-column.sql 
CREATE TABLE coupons (
  code        VARCHAR(12) PRIMARY KEY,
  valid_from  DATE NOT NULL,
  valid_to    DATE NOT NULL,
  pct_off     TINYINT,
  flat_off    DECIMAL(8,2),
  CONSTRAINT chk_dates  CHECK (valid_to > valid_from),
  CONSTRAINT chk_one_discount CHECK ((pct_off IS NULL) <> (flat_off IS NULL)),
  CONSTRAINT chk_pct    CHECK (pct_off BETWEEN 1 AND 90)
);
INSERT INTO coupons VALUES ('SPRING10', '2026-03-01', '2026-03-31', 10, NULL);
INSERT INTO coupons VALUES ('OOPS', '2026-03-31', '2026-03-01', 5, NULL);
INSERT INTO coupons VALUES ('BOTH', '2026-04-01', '2026-04-30', 5, 100);
INSERT INTO coupons VALUES ('NONE', '2026-04-01', '2026-04-30', NULL, NULL);
Output 
Query OK, 0 rows affected
Query OK, 1 row affected
ERROR 3819 (HY000): Check constraint 'chk_dates' is violated.
ERROR 3819 (HY000): Check constraint 'chk_one_discount' is violated.
ERROR 3819 (HY000): Check constraint 'chk_one_discount' is violated.

chk_one_discount says exactly one of the two discount columns must be filled.

5. Adding, Dropping and Disabling Checks

Existing rows must satisfy a new check. Our table contains a product called Cap, and we now want names of at least 4 characters:

add-fails.sql 
INSERT INTO products VALUES ('CAP-1', 'Cap', 250, 0, 30, NULL);
ALTER TABLE products ADD CONSTRAINT chk_name_len CHECK (CHAR_LENGTH(name) >= 4);
Output 
Query OK, 1 row affected
ERROR 3819 (HY000): Check constraint 'chk_name_len' is violated.
query.sql 
SELECT sku, name FROM products WHERE CHAR_LENGTH(name) < 4;

Rows that break the new rule

skuname
CAP-1Cap
fix-then-add.sql 
UPDATE products SET name = 'Sun Cap' WHERE sku = 'CAP-1';
ALTER TABLE products ADD CONSTRAINT chk_name_len CHECK (CHAR_LENGTH(name) >= 4);
Output 
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

A check can be switched off without dropping it, for example during a data migration. Turning it back on re-validates the existing rows:

not-enforced.sql 
ALTER TABLE products ALTER CHECK chk_name_len NOT ENFORCED;
INSERT INTO products VALUES ('X1', 'Pen', 20, 0, 10, NULL);    -- allowed while not enforced
ALTER TABLE products ALTER CHECK chk_name_len ENFORCED;        -- fails: 'Pen' breaks the rule
Output 
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
Query OK, 1 row affected
ERROR 3819 (HY000): Check constraint 'chk_name_len' is violated.
enforce-and-drop.sql 
DELETE FROM products WHERE sku = 'X1';
ALTER TABLE products ALTER CHECK chk_name_len ENFORCED;
ALTER TABLE products DROP CHECK chk_name_len;
Output 
Query OK, 1 row affected
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

6. What You Can't Use in a CHECK

  • Sub-queries and references to other tables (use a foreign key or a trigger instead).
  • Non-deterministic functions such as NOW(), CURDATE(), RAND() or UUID().
  • Stored functions, user variables and AUTO_INCREMENT columns.
query.sql 
CREATE TABLE bookings (id INT PRIMARY KEY, travel_date DATE CHECK (travel_date >= CURDATE()));
Output 
ERROR 3814 (HY000): An expression of a check constraint 'bookings_chk_1' contains disallowed function: curdate.
For "not in the past" rules, validate in the application or a BEFORE INSERT trigger.

7. Listing CHECK Constraints

query.sql 
SELECT tc.table_name, cc.constraint_name, cc.check_clause, tc.enforced
FROM information_schema.check_constraints cc
JOIN information_schema.table_constraints tc
  ON tc.constraint_schema = cc.constraint_schema AND tc.constraint_name = cc.constraint_name
WHERE cc.constraint_schema = 'shop'
ORDER BY tc.table_name, cc.constraint_name;

Result: 7 rows

TABLE_NAMECONSTRAINT_NAMECHECK_CLAUSEENFORCED
couponschk_dates(`valid_to` > `valid_from`)YES
couponschk_one_discount((`pct_off` is null) <> (`flat_off` is null))YES
couponschk_pct(`pct_off` between 1 and 90)YES
productschk_discount((`discount` >= 0) and (`discount` <= `price`))YES
productsproducts_chk_1(`price` > 0)YES
productsproducts_chk_2(`stock` >= 0)YES
productsproducts_chk_3(`rating` between 1 and 5)YES

8. Summary

  • CHECK (condition) rejects rows where the condition is FALSE (enforced since 8.0.16).
  • NULL results pass; combine with NOT NULL when needed.
  • Table-level checks can compare several columns.
  • Add with ADD CONSTRAINT … CHECK, disable with ALTER CHECK … NOT ENFORCED, remove with DROP CHECK.
  • No sub-queries or time-dependent functions inside a CHECK.