SQL error

Fix “CHECK Constraint Failed” in SQL

A value is storable but breaks a business rule the table enforces. Read the expression.

The symptom

A CHECK constraint is a business rule written into the table: a quantity must be positive, a status must be one of four words, a ship date cannot precede an order date. The value you tried to write fits the column's type and breaks the rule.

The garden_shop practice tables load straight from CSV with no rules attached, so the bench below declares one to break.

The message quotes the rule

DuckDB prints the expression back at you: Constraint Error: CHECK constraint failed on table order_imports with expression CHECK((quantity > 0)). That is the whole diagnosis on one line - you know the table, and you know which rule fired even when the table has several.

Which matters, because a table usually does have several. A named table-level CHECK can compare two columns, something a column-level check cannot do.

Several rules on one table

create or replace table order_imports (
  order_id integer,
  order_date date,
  shipped_date date,
  quantity integer check (quantity > 0),
  status varchar check (status in ('pending', 'shipped', 'cancelled')),
  check (shipped_date is null or shipped_date >= order_date)
);

insert into order_imports
values (1, date '2024-07-01', date '2024-06-28', 3, 'shipped');

That insert has a positive quantity and a valid status, so the failure is the unnamed table-level rule, and the message says so by quoting it back.

Find every row that would fail

Turn the quoted expression into a WHERE clause and negate it. The rows it returns are exactly the rows the constraint will reject, which is more useful than discovering them one insert at a time.

Negate the rule to list the offenders

select order_id,
       order_date,
       shipped_date
from orders
where not (shipped_date is null or shipped_date >= order_date)
order by order_id

Careful with NOT and NULL

Negating a rule that involves a nullable column is not as simple as wrapping it in NOT. A CHECK passes when its expression is trueor unknown, so a row where the expression evaluates to NULL is accepted by the constraint but is not returned by NOT (...) either. Handle the null case explicitly, the way the rule above does withshipped_date is null.

Fix the data, or fix the rule

Most of the time the data is wrong and the rule is doing its job: correct the value, or filter the bad rows out of the load and report them. Change the constraint only when the rule itself no longer matches the business - and if you find yourself widening a rule to get one row in, that is usually the row telling you something.

Load only the rows that pass

create or replace table order_imports (
  order_id integer,
  quantity integer check (quantity > 0)
);

insert into order_imports
select oi.order_id,
       oi.quantity
from order_items oi
where oi.quantity > 0;

select count(*) as loaded
from order_imports;

How other engines word it

Engine	Message
DuckDB	`Constraint Error: CHECK constraint failed on table order_imports with expression CHECK((quantity > 0))`
PostgreSQL	`ERROR: new row for relation "order_imports" violates check constraint "order_imports_quantity_check"`
MySQL	`Check constraint 'order_imports_chk_1' is violated.`
SQL Server	`The INSERT statement conflicted with the CHECK constraint "CK_order_imports_quantity".`
SQLite	`CHECK constraint failed: order_imports`