Fix “CHECK Constraint Failed” in SQL

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 can have several checks. A table-level CHECK is often the clearest form for a rule that compares columns, although DuckDB also permits a column-attached check to reference another column.

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 test for false. Those are the rows this check rejects; rows where the expression is NULL are accepted unless another constraint rejects them.

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

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. If you find yourself widening a rule to get one row in, that is usually the row telling you something.

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

Related errors

• Fix “NOT NULL Constraint Failed” in SQL

  A required column got no value. Supply one, give the column a default, or let it be null.

• Fix “Violates Foreign Key Constraint” in SQL

  A child row points at a parent that is not there, or a parent still has children.

• Fix “Failing Row Contains” Constraint Errors in SQL

  An INSERT or UPDATE broke a NOT NULL or CHECK rule on the table. The detail line shows the row.

Learn more

• SQL error messages explained
• Reference · Table constraints
• Lesson 18.2 · Check data quality with SQL
• Lesson 6.4 · Business rules in SQL