MySQL CHECK Constraint

MySQL CHECK Constraint: Validating Column Rules

The MySQL CHECK constraint enforces a boolean expression on every row inserted or updated in a table. If an INSERT or UPDATE violates the CHECK expression, MySQL aborts the write with an error—protecting business invariants directly at the database layer.

Version Requirement Note

MySQL only began enforcing CHECK constraints in MySQL 8.0.16. Older MySQL 5.7 servers accepted CHECK (...) in CREATE TABLE syntax without throwing an error, yet silently ignored the validation rule.

Practical SQL Examples

You can define column-level checks (like positive prices) or table-level checks comparing two columns (such as ensuring sale prices never exceed regular prices):

CREATE TABLE demo_products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    sale_price DECIMAL(10, 2) NULL,
    stock_qty INT NOT NULL DEFAULT 0,
    CONSTRAINT chk_demo_products_price CHECK (price > 0),
    CONSTRAINT chk_demo_products_stock CHECK (stock_qty >= 0),
    CONSTRAINT chk_demo_products_sale CHECK (sale_price IS NULL OR sale_price <= price)
);

Common Mistakes and Best Practices

• Remember NULL Passes CHECK Constraints: A CHECK (price > 0) expression evaluates to UNKNOWN when price is NULL, and SQL CHECK only rejects rows that evaluate to FALSE. Combine NOT NULL with CHECK whenever missing values are invalid in your schema.
• Avoid Nondeterministic Functions Inside CHECK: MySQL forbids functions like NOW(), RAND(), or subqueries inside CHECK expressions because row validity cannot depend on external state or the current clock.
• Name Every CHECK Constraint: Explicit names like chk_demo_products_stock show up in error logs so developers immediately know which rule failed during inserts or migrations.
• Test Both Positive and Negative Cases: Verify that invalid inserts fail with constraint violation errors before deploying migrations to production.

Frequently Asked Questions

Can I add a CHECK constraint to an existing table?

Yes, run ALTER TABLE demo_products ADD CONSTRAINT chk_price_positive CHECK (price > 0); once all existing rows pass the condition.

How do I drop a CHECK constraint in MySQL 8?

Run ALTER TABLE demo_products DROP CHECK chk_demo_products_stock;.

Does CHECK run during INSERT IGNORE?

INSERT IGNORE downgrades CHECK constraint failures to warnings and skips inserting the invalid row.