Introduction

When a CHECK constraint appears to do nothing in MySQL, the first question is version support. Older MySQL releases accepted the syntax but did not enforce the rule, which made many schemas look valid while allowing bad data through. The fix depends on whether the problem is server version, table definition, or an assumption about what CHECK actually validates.

Verify Whether the Server Enforces CHECK

The biggest source of confusion is historical behavior. In older MySQL versions, especially before MySQL 8.0.16, CHECK expressions were parsed but effectively ignored. If you create a constraint on one of those servers, inserts can still succeed even when the condition is false.

You can verify the server version directly:

SELECT VERSION();

If the server is too old to enforce checks, the constraint text in your table definition is not enough. You need a different enforcement mechanism or an upgrade.

Define the Constraint Clearly

On a server that supports enforcement, the constraint must be written against the row being inserted or updated.

1CREATE TABLE products (
2    id INT PRIMARY KEY,
3    price DECIMAL(10, 2) NOT NULL,
4    quantity INT NOT NULL,
5    CONSTRAINT chk_price CHECK (price >= 0),
6    CONSTRAINT chk_quantity CHECK (quantity >= 0)
7);

A failing insert should now be rejected:

INSERT INTO products (id, price, quantity)
VALUES (1, -10.00, 5);

The rule is evaluated per row. It is not a general-purpose business-logic engine. Keep the condition simple, deterministic, and tied to the columns in that record.

Know What CHECK Is Good At

CHECK is appropriate for invariants such as nonnegative numbers, valid ranges, or simple relationships between fields in the same row.

1CREATE TABLE bookings (
2    id INT PRIMARY KEY,
3    start_day INT NOT NULL,
4    end_day INT NOT NULL,
5    CONSTRAINT chk_day_order CHECK (end_day >= start_day)
6);

This is a good use case because the rule is local to the row and easy for the database to enforce consistently.

What CHECK is not good at is cross-table validation, complex workflow rules, or logic that depends on external state. For those cases, use other database features or application logic.

Alternatives on Older MySQL Servers

If you are on an older MySQL version, the usual substitutes are triggers and application-side validation.

1DELIMITER //
2
3CREATE TRIGGER products_before_insert
4BEFORE INSERT ON products
5FOR EACH ROW
6BEGIN
7    IF NEW.price < 0 THEN
8        SIGNAL SQLSTATE '45000'
9            SET MESSAGE_TEXT = 'price must be nonnegative';
10    END IF;
11END //
12
13DELIMITER ;

Triggers are more verbose than a declarative CHECK, but they at least enforce the rule inside the database when native support is missing.

Application validation is still useful, but it should not be the only line of defense if multiple clients write to the same database.

Troubleshooting a Constraint That Still Seems Broken

If the server version is correct and inserts still succeed, inspect the table definition that actually exists in the database.

SHOW CREATE TABLE products;

This confirms whether the constraint was created as you expected. It also catches cases where a migration was not applied to the environment you are testing.

Be careful with assumptions about existing data as well. Adding a new constraint to a dirty table may fail or require cleanup, depending on how the migration is executed. Constraint design is part of schema management, not only query writing.

Common Pitfalls

• Assuming every MySQL server enforces CHECK constraints the same way.
• Writing a CHECK on an older server where the syntax is accepted but not enforced.
• Using CHECK for complex business rules that belong in triggers or application code.
• Forgetting to verify the actual deployed table definition with SHOW CREATE TABLE.
• Relying on client-side validation alone when multiple systems can write to the database.

Summary

• First confirm whether your MySQL version actually enforces CHECK.
• Use CHECK for simple row-level invariants such as ranges and field relationships.
• Test the constraint with an insert that should fail.
• On older servers, use triggers or upgrade to a version with enforcement.
• Verify the real schema in the target environment before assuming the constraint exists.