CHECK Constraints in MySQL

VillageSQL is a drop-in replacement for MySQL with extensions.

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:

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:

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:

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.:

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:

ALTER TABLE products
ADD CONSTRAINT chk_name_length CHECK (CHAR_LENGTH(name) >= 2);

Drop a named constraint:

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:

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:

ALTER TABLE products ALTER CHECK chk_price NOT ENFORCED;

Re-enable it:

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

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

Defining CHECK Constraints

Enforcement

Adding and Removing Constraints

Disabling Enforcement

CHECK vs Triggers vs Application Validation

Frequently Asked Questions

Do CHECK constraints work on all storage engines?

Can CHECK constraints reference other tables?

Were CHECK constraints always in MySQL?

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​Defining CHECK Constraints

​Enforcement

​Adding and Removing Constraints

​Disabling Enforcement

​CHECK vs Triggers vs Application Validation

​Frequently Asked Questions

​Do CHECK constraints work on all storage engines?

​Can CHECK constraints reference other tables?

​Were CHECK constraints always in MySQL?

​Troubleshooting

​See also

Defining CHECK Constraints

Enforcement

Adding and Removing Constraints

Disabling Enforcement

CHECK vs Triggers vs Application Validation

Frequently Asked Questions

Do CHECK constraints work on all storage engines?

Can CHECK constraints reference other tables?

Were CHECK constraints always in MySQL?

Troubleshooting

See also