> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # CHECK Constraints in MySQL > How MySQL CHECK constraints work: syntax, enforcement behavior, named constraints, disabling constraints, and how CHECK compares to triggers and application-level validation. All examples in this guide work on VillageSQL. Install Now → MySQL enforces `CHECK` constraints. The syntax was accepted in older versions but silently ignored — a behavior that surprised many developers migrating from PostgreSQL. ## Defining CHECK Constraints Add a `CHECK` constraint inline with the column or as a table-level constraint: ```sql theme={null} CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, quantity INT NOT NULL, -- Inline constraints CONSTRAINT chk_price CHECK (price > 0), CONSTRAINT chk_quantity CHECK (quantity >= 0) ); ``` Or inline with the column definition: ```sql theme={null} CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, status VARCHAR(20) CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')), discount DECIMAL(5,2) CHECK (discount BETWEEN 0 AND 100) ); ``` Table-level constraints are necessary when the check expression spans multiple columns: ```sql theme={null} CREATE TABLE shipments ( id INT AUTO_INCREMENT PRIMARY KEY, shipped_at DATE, delivered_at DATE, CONSTRAINT chk_dates CHECK (delivered_at IS NULL OR delivered_at >= shipped_at) ); ``` ## Enforcement MySQL enforces `CHECK` constraints on `INSERT` and `UPDATE`. A violation returns `ERROR 3819 (HY000): Check constraint 'name' is violated.`: ```sql theme={null} INSERT INTO products (name, price, quantity) VALUES ('Widget', -5.00, 10); -- ERROR 3819 (HY000): Check constraint 'chk_price' is violated. ``` The error message includes the constraint name, which is why naming constraints is worth the extra typing. `CHECK` constraints are **not** evaluated on `DELETE`. They're also not re-evaluated when the constraint itself is added to an existing table — adding a constraint to a table with existing data that violates it will fail immediately. ## Adding and Removing Constraints Add a constraint to an existing table: ```sql theme={null} ALTER TABLE products ADD CONSTRAINT chk_name_length CHECK (CHAR_LENGTH(name) >= 2); ``` Drop a named constraint: ```sql theme={null} ALTER TABLE products DROP CHECK chk_price; -- or the SQL standard syntax: ALTER TABLE products DROP CONSTRAINT chk_price; ``` List all constraints on a table: ```sql theme={null} SELECT cc.constraint_name, cc.check_clause 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 = DATABASE() AND tc.table_name = 'products'; ``` ## Disabling Enforcement Disable a specific constraint without dropping it: ```sql theme={null} ALTER TABLE products ALTER CHECK chk_price NOT ENFORCED; ``` Re-enable it: ```sql theme={null} ALTER TABLE products ALTER CHECK chk_price ENFORCED; ``` `NOT ENFORCED` is useful during data migrations when you need to temporarily bypass validation. Re-enable enforcement when the migration is complete. ## CHECK vs Triggers vs Application Validation | | CHECK constraint | Trigger | Application code | | :------------------ | :----------------------------- | :------------------------------- | :------------------------------- | | Enforced by | MySQL (always) | MySQL (always) | Application (only when app runs) | | Performance | Very low overhead | Per-row execution overhead | Round-trip cost | | Error visibility | SQL error with constraint name | SIGNAL required to surface error | Application-defined | | Cross-column checks | Yes | Yes | Yes | | Cross-table checks | No | Yes (can query other tables) | Yes | | Complexity | Simple expressions only | Arbitrary SQL logic | Arbitrary code | Use `CHECK` for simple, self-contained rules: valid ranges, allowed values, non-negative numbers, date ordering. Use a trigger when the rule requires querying other tables. Use application validation for rules involving external state (rate limits, availability checks). ## Frequently Asked Questions #### Do CHECK constraints work on all storage engines? Yes. Unlike some MySQL features, `CHECK` constraints work on InnoDB, MyISAM, and other storage engines. However, MyISAM tables can have defined constraints that are syntactically valid but this storage engine does not have rollback, so a failed constraint on MyISAM can leave partial data. InnoDB handles violations cleanly with full rollback. #### Can CHECK constraints reference other tables? No. `CHECK` expressions are limited to the current row of the current table. Subqueries, stored functions, and references to other tables are not allowed in `CHECK` expressions. Use a trigger for cross-table validation. #### Were CHECK constraints always in MySQL? The syntax was accepted in older MySQL versions, but constraints were silently ignored. If you're migrating from an older schema, verify that existing `CHECK` clauses are valid and contain rules you want enforced. ## Troubleshooting | Problem | Solution | | :---------------------------------------------- | :--------------------------------------------------------------------------------------------------------------------------- | | `ERROR 3819: Check constraint violated` | The INSERT or UPDATE value fails the constraint — check the constraint name in the error to identify which rule was violated | | `ALTER TABLE ADD CONSTRAINT` fails immediately | Existing rows in the table violate the constraint — fix the data first or use `NOT ENFORCED` during migration | | CHECK constraint defined but not being enforced | Running an old MySQL version that predates enforcement — upgrade or use triggers | | Cannot drop a constraint | Use the constraint name (not the column name) with `DROP CHECK` or `DROP CONSTRAINT` | | Constraint name unknown | Query `information_schema.check_constraints` to find constraint names for the table | ## See also * [Normalization in MySQL](/guides/normalization) — schema design that CHECK constraints complement * [MySQL Triggers](/guides/triggers) — an alternative to CHECK constraints for complex validation * [NULL in MySQL](/guides/null-in-mysql) — CHECK constraints and NULL interact in non-obvious ways