A MySQL CHECK constraint exists but bad rows still go in
Check 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.
Fix it fast
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\G2Match what you see, then jump to its fix
- 1
SELECT VERSION() returns 5.7 or 8.0.15 or lowerThe server is older than 8.0.16Fix - 2
SHOW CREATE TABLE prints NOT ENFORCED after the CHECK expressionThe constraint was created as NOT ENFORCEDFix - 3
Only rows with NULL in the checked column get throughThe value is NULL, so the condition is UNKNOWN, not FALSEFix - 4
The 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
- 1
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
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\G2Fix
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
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix