by
SQL Tutorial › Database Design › CHECK

SQL CHECK Constraint

IntermediateLesson 55 of 698 min read7 queries run on MySQL 8.4

A CHECK constraint validates each row against a condition: prices must be positive, ratings between 1 and 5, an end date after its start date. MySQL accepted the syntax for years but ignored it; since MySQL 8.0.16 checks are enforced, so they are now a real tool for keeping data valid.

Advertisement
In this lesson
  • Validate values with CHECK conditions
  • Write rules across several columns
  • Remember how CHECK treats NULL
  • Name, add, drop and disable checks

A column check

SQL
CREATE TABLE ratings (  rating_id INT PRIMARY KEY,  stars     INT NOT NULL CHECK (stars BETWEEN 1 AND 5),  comment   VARCHAR(200));INSERT INTO ratings VALUES (1, 5, 'Great course'), (2, 3, NULL);SELECT * FROM ratings;
ResultMySQL 8.4
mysql> CREATE TABLE ratings (
Query OK, 0 rows affected
mysql> INSERT INTO ratings VALUES (1, 5, 'Great course'), (2, 3, NULL)
Query OK, 2 rows affected
mysql> SELECT * FROM ratings
rating_idstarscomment
15Great course
23NULL
2 rows in set
MySQL
CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT NOT NULL CHECK (stars BETWEEN 1 AND 5));INSERT INTO ratings VALUES (3, 7);
ResultMySQL 8.4
mysql> CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT NOT NULL CH…
Query OK, 0 rows affected
mysql> INSERT INTO ratings VALUES (3, 7)
ERROR 3819 (HY000): Check constraint 'ratings_chk_1' is violated.
How this query works, step by step
  1. Every insert or update is tested with stars BETWEEN 1 AND 5.
  2. 7 fails, so MySQL rejects the row with error 3819 and names the check (ratings_chk_1).
Advertisement

A rule across columns

A table-level check can compare columns, and a name makes the error clearer:

MySQL
CREATE TABLE promotions (  promo_id   INT PRIMARY KEY,  discount   INT NOT NULL,  start_date DATE NOT NULL,  end_date   DATE NOT NULL,  CONSTRAINT chk_discount CHECK (discount > 0 AND discount <= 70),  CONSTRAINT chk_dates CHECK (end_date >= start_date));INSERT INTO promotions VALUES (1, 20, '2026-01-01', '2026-01-31');INSERT INTO promotions VALUES (2, 10, '2026-03-10', '2026-03-01');
ResultMySQL 8.4
mysql> CREATE TABLE promotions (
Query OK, 0 rows affected
mysql> INSERT INTO promotions VALUES (1, 20, '2026-01-01', '2026-01-31')
Query OK, 1 row affected
mysql> INSERT INTO promotions VALUES (2, 10, '2026-03-10', '2026-03-01')
ERROR 3819 (HY000): Check constraint 'chk_dates' is violated.
How this query works, step by step
  1. Promotion 1 passes both checks.
  2. Promotion 2 ends before it starts, so chk_dates rejects it, and the error message names it.

A list of allowed values:

SQL
CREATE TABLE shipments (  shipment_id INT PRIMARY KEY,  status VARCHAR(12) NOT NULL CHECK (status IN ('packed', 'shipped', 'delivered')));INSERT INTO shipments VALUES (1, 'packed'), (2, 'shipped');SELECT * FROM shipments;
ResultMySQL 8.4
mysql> CREATE TABLE shipments (
Query OK, 0 rows affected
mysql> INSERT INTO shipments VALUES (1, 'packed'), (2, 'shipped')
Query OK, 2 rows affected
mysql> SELECT * FROM shipments
shipment_idstatus
1packed
2shipped
2 rows in set
Advertisement

CHECK and NULL

A check passes when its condition is UNKNOWN, which happens when a value is NULL:

SQL
CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT CHECK (stars BETWEEN 1 AND 5));INSERT INTO ratings VALUES (1, NULL);SELECT * FROM ratings;
ResultMySQL 8.4
mysql> CREATE TABLE ratings (rating_id INT PRIMARY KEY, stars INT CHECK (star…
Query OK, 0 rows affected
mysql> INSERT INTO ratings VALUES (1, NULL)
Query OK, 1 row affected
mysql> SELECT * FROM ratings
rating_idstars
1NULL
1 row in set

The row is accepted because NULL BETWEEN 1 AND 5 is UNKNOWN, not FALSE. Add NOT NULL when the value is required.

Add, drop and disable a check

MySQL
ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);SELECT constraint_name, check_clause FROM information_schema.check_constraintsWHERE constraint_schema = 'company';
ResultMySQL 8.4
mysql> ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0)
Query OK, 9 rows affected
mysql> SELECT constraint_name, check_clause FROM information_schema.check_con…
CONSTRAINT_NAMECHECK_CLAUSE
chk_stock(`stock` >= 0)
1 row in set
MySQL
ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);UPDATE products SET stock = -5 WHERE product_id = 1;
ResultMySQL 8.4
mysql> ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0)
Query OK, 9 rows affected
mysql> UPDATE products SET stock = -5 WHERE product_id = 1
ERROR 3819 (HY000): Check constraint 'chk_stock' is violated.
  • ALTER TABLE products DROP CHECK chk_stock; removes it.
  • ALTER TABLE products ALTER CHECK chk_stock NOT ENFORCED; keeps it but stops checking (useful for bulk loads).
Note

A check may use only the row’s own columns and deterministic functions. It cannot use subqueries, other tables, NOW() or user variables. For rules that involve other tables, use a foreign key or a trigger.

Quick quiz

1. From which MySQL version are CHECK constraints enforced?

2. A column has CHECK (age >= 18). Is a NULL age accepted?

3. Can a CHECK constraint compare two columns of the same row?

Advertisement

CHECK FAQ

Does MySQL support CHECK constraints?

Yes. MySQL 8.0.16 and later enforce CHECK constraints; older versions ignored them.

Can a CHECK constraint reference another table?

No. Checks see only the current row. Use a foreign key or a trigger for cross-table rules.

What is the difference between CHECK and ENUM?

ENUM is a data type that restricts a column to a list. A CHECK can express any row-level rule, including ranges and comparisons between columns, and keeps the column a normal type.