Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A MySQL CHECK constraint exists but bad rows still go in

ErrorCheck constraint 'chk_price' is violated.

Short answer

A CHECK constraint only blocks rows when the server enforces it and the condition evaluates to FALSE. Servers before 8.0.16 parse the clause and ignore it. Newer servers skip a constraint marked NOT ENFORCED, let NULL pass because it evaluates to UNKNOWN, and turn violations into warnings under IGNORE.

  • Applies to MySQL 8.0.16 and later, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the server version and the table definition together. The definition shows whether the CHECK clause is there and whether it says NOT ENFORCED.

    SELECT VERSION();
    SHOW CREATE TABLE orders\G
  2. 2Match what you see, then jump to its fix

    1. 1SELECT VERSION() returns 5.7 or 8.0.15 or lowerThe server is older than 8.0.16Fix
    2. 2SHOW CREATE TABLE prints NOT ENFORCED after the CHECK expressionThe constraint was created as NOT ENFORCEDFix
    3. 3Only rows with NULL in the checked column get throughThe value is NULL, so the condition is UNKNOWN, not FALSEFix
    4. 4The statement ends with a warning instead of an error, and the bad row is missingINSERT IGNORE or LOAD DATA turns the violation into a warningFix

What does the error look like?

  • Check constraint 'chk_price' is violated.

Cause 1 of 4

The server is older than 8.0.16

1Check

Before 8.0.16 MySQL permitted a limited CHECK syntax but parsed and ignored it. From 8.0.16 it implements table and column CHECK constraints for all storage engines.

SELECT VERSION();

2Fix

Upgrade to a current release (8.4 LTS or newer), then add the constraint again. Constraints written for the old server only existed on paper.

ALTER TABLE orders
  ADD CONSTRAINT chk_price CHECK (price >= 0);

3Verify

An insert that breaks the rule must now fail with error 3819.

INSERT INTO orders (price) VALUES (-1);

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 4

The constraint was created as NOT ENFORCED

1Check

A constraint declared NOT ENFORCED is created but never evaluated.

SHOW CREATE TABLE orders\G

2Fix

Switch it to enforced. MySQL checks existing rows first, so if some already break the rule the statement fails and you must clean those rows before enforcing.

ALTER TABLE orders ALTER CHECK chk_price ENFORCED;

3Verify

Repeat the insert that should be refused.

INSERT INTO orders (price) VALUES (-1);

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 4

The value is NULL, so the condition is UNKNOWN, not FALSE

1Check

A CHECK passes when the expression is TRUE or UNKNOWN. Any comparison with NULL is UNKNOWN.

SELECT COUNT(*) FROM orders WHERE price IS NULL;

2Fix

If NULL must not be allowed, say so on the column itself. Fix existing NULL rows first, then forbid new ones.

UPDATE orders SET price = 0 WHERE price IS NULL;
ALTER TABLE orders MODIFY price DECIMAL(10,2) NOT NULL;

3Verify

An insert with a NULL price now fails on the NOT NULL rule.

INSERT INTO orders (price) VALUES (NULL);

Confirmed by dev.mysql.com

Did this fix it?

Cause 4 of 4

INSERT IGNORE or LOAD DATA turns the violation into a warning

1Check

For IGNORE statements and non-restrictive loads, a FALSE condition raises a warning and the offending row is skipped. The row is not stored, but the statement does not fail.

SHOW WARNINGS;

2Fix

Remove IGNORE where you want a hard failure, or read SHOW WARNINGS after each bulk load.

INSERT INTO orders (price) VALUES (-1);

3Verify

The plain INSERT now returns error 3819 instead of a warning.

SHOW WARNINGS;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Name every constraint so the error message and ALTER statements are readable.
  • Check SELECT VERSION() on every environment: a staging server on an old release hides violations that production rejects.
  • Pair a CHECK with NOT NULL on the same column when NULL is not a valid value.
  • Run SHOW WARNINGS after any IGNORE or bulk load.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.