CHECK Constraints
CHECK puts domain rules where every writer must obey them; implemented in MySQL 8.0.16, and a NULL result always passes.
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]A CHECK constraint is a boolean expression the server evaluates on every insert and update. If it evaluates to FALSE the statement fails with error 3819, ER_CHECK_CONSTRAINT_VIOLATED.
It exists because "the application validates that" is only true of the application you have in mind. A migration, a bulk load, an admin console, a second service and a colleague with a MySQL client all write to the same table. A constraint is the one rule none of them can route around.
Column form and table form
A column constraint may reference only its own column; a table constraint may reference any column of the row. Both produce the same kind of object.
qty INT NOT NULL CHECK (qty > 0), -- column form, auto-named orders_chk_1
CONSTRAINT chk_orders_qty CHECK (qty > 0), -- table form, same rule, your name
CONSTRAINT chk_orders_span CHECK (ends_at > starts_at) -- two columns: table form onlyUnnamed constraints are named tbl_chk_N in definition order. Constraint names are unique per schema, and the name is what ALTER TABLE ... DROP CHECK symbol takes.
What the expression may contain
It must be deterministic and self-contained: no subqueries, no other tables, no stored or loadable functions, no system or user variables, no AUTO_INCREMENT column, and no non-deterministic built-in such as NOW() or CURRENT_USER(). So "not in the past" cannot be expressed; "not before 2000-01-01" can.
CHECK was implemented in MySQL 8.0.16. On 5.7 the identical syntax parses and is silently ignored — a schema file that looks constrained and is not.
Example
CREATE TABLE subscriptions (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
account_id BIGINT UNSIGNED NOT NULL,
plan VARCHAR(20) NOT NULL,
price_cents INT UNSIGNED NOT NULL,
seats INT NOT NULL,
starts_on DATE NOT NULL,
ends_on DATE NULL,
PRIMARY KEY (id),
-- Domain rule: these three strings are the only plans that exist.
CONSTRAINT chk_subs_plan CHECK (plan IN ('free','team','enterprise')),
-- seats is NOT NULL, so this range can never be skipped by a NULL value.
CONSTRAINT chk_subs_seats CHECK (seats BETWEEN 1 AND 500),
-- Cross-column rules must be table-level. ends_on is nullable for
-- open-ended plans, and the explicit IS NULL branch says so on purpose
-- rather than relying on the comparison quietly yielding NULL.
CONSTRAINT chk_subs_span CHECK (ends_on IS NULL OR ends_on > starts_on),
-- Conditional rule: free plans are never charged for. Written as an
-- implication (NOT free OR price is zero) because CHECK has no IF.
CONSTRAINT chk_subs_free CHECK (plan <> 'free' OR price_cents = 0)
) ENGINE=InnoDB;When to use it
- Keep a status column to a known set of strings without changing the column type each time the set grows.
- Refuse negative prices and quantities at the database, so a bad API deploy cannot write them.
- Require ends_at to be after starts_at on every date range in the table.
- Validate the shape of a JSON payload column with JSON_SCHEMA_VALID before it is stored.
More examples
Adding a constraint to data that does not satisfy it yet
The three-step path for retrofitting a rule onto a live table. NOT ENFORCED also gives you a documented, greppable statement of intent while the backfill job runs, which a comment in application code does not. The same ALTER CHECK ... NOT ENFORCED is the escape hatch when a one-off data repair legitimately has to break the rule.
-- An enforced CHECK added to a populated table is validated against the
-- existing rows, and the ALTER fails if any row breaks it. NOT ENFORCED
-- records the rule without validating or enforcing it.
ALTER TABLE invoices
ADD CONSTRAINT chk_invoices_total CHECK (total_cents >= 0) NOT ENFORCED;
-- Find the offenders. Write the predicate exactly as the constraint does,
-- negated, so you are looking at the same rows the server would reject.
SELECT id, account_id, total_cents
FROM invoices
WHERE NOT (total_cents >= 0);
-- Fix them, then switch the constraint on. From here the server rejects
-- any new violation.
ALTER TABLE invoices ALTER CHECK chk_invoices_total ENFORCED;ENUM or CHECK for a fixed set of values
Use ENUM when the list is genuinely fixed and storage matters. Use VARCHAR plus CHECK when the list changes occasionally, when the value is read by other systems that would rather see a string, or when you want the allowed set visible as a constraint rather than buried in a column type.
-- Type-level: the set lives in the column definition. Adding a value is an
-- ALTER of the column type, and the stored value is an internal index.
status ENUM('open','pending','closed') NOT NULL
-- Constraint-level: the column stays a plain string, and the rule is a
-- named object you can drop and replace without touching the column.
status VARCHAR(16) NOT NULL,
CONSTRAINT chk_tickets_status CHECK (status IN ('open','pending','closed'))
-- Widening the CHECK version: drop the old rule, add the new one.
ALTER TABLE tickets
DROP CHECK chk_tickets_status,
ADD CONSTRAINT chk_tickets_status
CHECK (status IN ('open','pending','closed','escalated'));Validating the shape of a JSON column
JSON_SCHEMA_VALID(schema, document) returns 1 or 0 and is deterministic, so it is legal inside a CHECK. This is how you keep a JSON column from becoming a place where any shape at all can be written; JSON_SCHEMA_VALIDATION_REPORT(schema, document) returns a JSON report explaining which part failed, which is what you call when a rejected insert needs diagnosing.
CREATE TABLE device_readings (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
payload JSON NOT NULL,
PRIMARY KEY (id),
-- The schema has to be written inline: a CHECK expression cannot
-- reference a variable or read it from another table.
CONSTRAINT chk_readings_payload CHECK (
JSON_SCHEMA_VALID(
'{
"type": "object",
"properties": {
"latitude": {"type": "number", "minimum": -90, "maximum": 90},
"longitude": {"type": "number", "minimum": -180, "maximum": 180}
},
"required": ["latitude", "longitude"]
}',
payload
)
)
) ENGINE=InnoDB;
Discussion