UNIQUE, NOT NULL, CHECK

UNIQUE, NOT NULL, and CHECK Constraints

NOT NULL makes a value mandatory, UNIQUE rejects duplicates but allows many NULLs, and CHECK (expr) rejects rows for which the expression is false (NULL passes). MySQL 524 ignored CHECK until 8.0.16; it is now enforced on every insert and update.

Violating the constraints of the bookshop schemaSQL

INSERT INTO reviews (product_id, customer_id, rating, created_at) VALUES (1, 2, 6, NOW());
INSERT INTO customers (email, name, country) VALUES (NULL, 'Nobody', 'US');
ALTER TABLE orders ADD COLUMN shipped_at DATETIME NULL,
  ADD CONSTRAINT chk_shipped_after CHECK (shipped_at IS NULL OR shipped_at >= ordered_at);
UPDATE orders SET shipped_at = '2026-03-01 09:00:00' WHERE id = 1;
ALTER TABLE orders ADD CONSTRAINT chk_past CHECK (ordered_at <= NOW());

Output

ERROR 3819 (HY000): Check constraint 'reviews_chk_1' is violated.
ERROR 1048 (23000): Column 'email' cannot be null
Query OK, 9 rows affected (0.124 sec)
Records: 9  Duplicates: 0  Warnings: 0
ERROR 3819 (HY000): Check constraint 'chk_shipped_after' is violated.
ERROR 3814 (HY000): An expression of a check constraint 'chk_past' contains disallowed
  function: now.

Name your checks: the error reports the name, and DROP CHECK needs it. Expressions must be deterministic, so NOW(), RAND() and subqueries are refused. ALTER CHECK chk_shipped_after NOT ENFORCED suspends a check for a bulk import; switching back to ENFORCED re-validates every row and failed with error 3819 until the bad row was fixed.

MENU

UNIQUE, NOT NULL, CHECK