MySQL: ERROR 3819 (HY000): Check constraint is violated

What it means

A CHECK constraint is a rule every row must satisfy, written as an expression: price > 0, discount < price, qty >= 0. When an INSERT or UPDATE would store a row for which the expression is false, the server refuses the statement and names the constraint. Nothing is changed.

Three things to know:

MySQL enforces CHECK from 8.0.16. Earlier versions accepted the syntax and ignored it, so a schema or application moved from MySQL 5.7 can start failing on data it used to accept.
NULL passes. A check is only violated when the expression is false; with a NULL value it’s unknown, and the row is accepted. Use NOT NULL as well if the value is required.
Unnamed constraints get generated names. MySQL calls them <table>_chk_1, _chk_2…; MariaDB names a column’s own check after the column (products.qty in its message).

MariaDB reports the same failure as a different error:

ERROR 4025 (23000): CONSTRAINT `chk_price_positive` failed for `seo_err_mysql`.`products`

Common causes

The value really is invalid: a negative quantity, a zero price, an end date before a start date.
Arithmetic in an UPDATE, such as qty = qty - 1 on a row already at 0.
A rule across columns (discount < price) broken by changing only one of them.
Adding a constraint with ALTER TABLE … ADD CONSTRAINT … CHECK to a table that already has rows breaking it.

How to fix it

Read the rule

SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = DATABASE();

CONSTRAINT_NAME	CHECK_CLAUSE
products_chk_1	(`qty` >= 0)
chk_price_positive	(`price` > 0)
chk_discount_lt_price	(`discount` < `price`)

SHOW CREATE TABLE products shows the same constraints with their table.

Fix the value

Send a value that satisfies the rule, or guard the update so it can’t break it:

UPDATE products SET qty = qty - 1 WHERE id = 4 AND qty > 0;

Check the affected-row count: 0 means there was nothing left to take.

Find existing rows that break a new constraint

Before adding a check, look for rows where its expression is false:

SELECT id, price, discount FROM products WHERE NOT (discount < price);

Fix them, then add the constraint.

Change or switch off a rule that’s wrong

Drop it and add the corrected one. ALTER TABLE products DROP CONSTRAINT chk_price_cap works on both servers (MySQL also accepts DROP CHECK). MySQL can also keep a constraint without enforcing it:

ALTER TABLE ck ALTER CHECK ck_x_pos NOT ENFORCED;

Turning it back on with ENFORCED checks the existing rows first and fails with 3819 while any breaks it. MariaDB has a session switch instead, SET SESSION check_constraint_checks = 0, which lets rows in without any warning.

Reproduce it

On MySQL 8.4.11:

CREATE TABLE products (id int PRIMARY KEY, price decimal(8,2) NOT NULL,
  discount decimal(8,2) NOT NULL DEFAULT 0,
  qty int NOT NULL DEFAULT 0 CHECK (qty >= 0),
  CONSTRAINT chk_price_positive CHECK (price > 0),
  CONSTRAINT chk_discount_lt_price CHECK (discount < price));
INSERT INTO products (id, price) VALUES (1, 0);
INSERT INTO products (id, price, discount) VALUES (2, 10, 15);
INSERT INTO products (id, price, qty) VALUES (3, 10, -1);
INSERT INTO products (id, price) VALUES (4, 10);
UPDATE products SET qty = qty - 1 WHERE id = 4;
ALTER TABLE products ADD CONSTRAINT chk_price_cap CHECK (price < 5);

ERROR 3819 (HY000): Check constraint 'chk_discount_lt_price' is violated.
ERROR 3819 (HY000): Check constraint 'chk_discount_lt_price' is violated.
ERROR 3819 (HY000): Check constraint 'products_chk_1' is violated.
ERROR 3819 (HY000): Check constraint 'products_chk_1' is violated.
ERROR 3819 (HY000): Check constraint 'chk_price_cap' is violated.

The first row broke both chk_price_positive and chk_discount_lt_price (0 isn’t less than 0); MySQL named one of them. In a table ck with CHECK (x > 0) on a nullable column, NULL was accepted and -1 refused. INSERT IGNORE turned the error into Warning 3819 and skipped the row.

MariaDB 11.4.13 refused the same statements with 4025, naming chk_price_positive for the first row and the column for the unnamed check:

ERROR 4025 (23000): CONSTRAINT `chk_price_positive` failed for `seo_err_mysql`.`products`
ERROR 4025 (23000): CONSTRAINT `chk_discount_lt_price` failed for `seo_err_mysql`.`products`
ERROR 4025 (23000): CONSTRAINT `products.qty` failed for `seo_err_mysql`.`products`

In Inlet

Inlet’s structure editor works with a table’s constraints and shows the DDL before it runs. Edits in the grid are staged until you commit, with Review showing the exact SQL first; if a change breaks a check, the server refuses it, and Inlet shows the error and links to this page.

ERROR 3819 (HY000): Check constraint is violated