MariaDB CHECK Constraints

Deterministic guide for declaring, naming, dropping, and reasoning about CHECK constraints in MariaDB. CHECK constraints are enforced (not parsed-and-ignored) since MariaDB 10.2.1, which precedes MySQL 8 by several years. This skill applies to 10.6-LTS, 10.11-LTS, 11.x, and 12.x.

Quick Reference

ALWAYS remember the following invariants:

• CHECK passes when the predicate evaluates to NULL. SQL three-valued logic (3VL) treats NULL as "unknown", and a CHECK only fails when the predicate is explicitly FALSE. A nullable column col INT CHECK (col > 0) will accept NULL. Combine with NOT NULL to require a value AND a positive value.
• CHECK constraints are enforced since MariaDB 10.2.1. Earlier versions parsed CHECK syntax but ignored it. MariaDB's enforcement predates MySQL 8.0 (released July 2018) by approximately 18 months.
• Use CHECK (JSON_VALID(col)) to validate JSON columns. MariaDB's JSON type is a LONGTEXT alias, not native binary; without JSON_VALID you can insert literal garbage and break every downstream JSON_EXTRACT. (See L-005 / D-010.) From 10.4.3+, the JSON alias auto-applies JSON_VALID as a CHECK, but explicit CHECK (JSON_VALID(col)) is required on LONGTEXT/TEXT columns used as JSON and recommended in all migration scripts.
• INSERT IGNORE turns CHECK violation into a warning. The row is not inserted, but no error is raised. Use plain INSERT when you need hard rejection.
• Prefer CHECK over triggers for pure invariants. CHECK is set-based, declarative, faster, and visible in information_schema.CHECK_CONSTRAINTS. Use triggers only when you need side-effects (audit row, modify other tables, derived columns) or cross-table validation.
• Cross-table or sub-query CHECK is not allowed. A CHECK predicate may only reference columns of the same row. Use a BEFORE INSERT/BEFORE UPDATE trigger with a SIGNAL if you must validate against another table.
• Disable globally with SET SESSION check_constraint_checks = OFF for bulk-load of legacy data, then re-enable. Disabled checks do not re-validate existing rows on re-enable.
• auto_increment columns may not appear in a CHECK constraint (per mariadb.com/kb/en/constraint/).
• Only deterministic functions are permitted in CHECK expressions. NOW(), RAND(), UUID(), CURRENT_TIMESTAMP ARE non-deterministic and MUST NOT appear in CHECK.

Decision Tree : CHECK vs NOT NULL vs Trigger vs App-Layer