SQLite: CHECK constraint failed

What it means

A CHECK constraint is an expression every row must satisfy, such as total >= 0. Your INSERT or UPDATE produced a row where it’s false, so SQLite stops that statement and undoes what it changed. A check that comes out NULL passes: CHECK (price > 0) allows a NULL price.

The message tells you which rule:

A named constraint, CONSTRAINT price_positive CHECK (price > 0), gives its name: CHECK constraint failed: price_positive.
An unnamed one gives its expression: CHECK constraint failed: total >= 0.

The result code is SQLITE_CONSTRAINT (19), extended code SQLITE_CONSTRAINT_CHECK (275).

Common causes

A value out of range: a negative amount, an end date before the start date, a percentage over 100.
A value not in the allowed list. status IN ('new', 'paid', 'shipped') compares exactly, so 'Paid' and 'paid ' fail.
Text where the rule expects a number. SQLite stores 'forty' in an INTEGER column as text, and text sorts after every number, so age BETWEEN 0 AND 150 is false. '42' is converted to the number 42 and passes.
A boolean sent as text. SQLite has no boolean type; a column checked with IN (0, 1) fails for 'true' or 'yes' from a form, CSV file or API.
Old rows meet a new rule. A table rebuild copies existing rows into a table with a new CHECK, or ALTER TABLE ADD COLUMN adds a column with a CHECK, which SQLite tests against every existing row since 3.37.0. Any old row that breaks it fails the whole change.

How to fix it

Read the rule

If the message gives a name, find the expression in the table’s definition:

SELECT sql FROM sqlite_schema WHERE name = 'orders';

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), total REAL CHECK (total >= 0), status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new','paid','shipped')))

Then compare the values you sent with it.

Fix the value

Correct it where it comes from: validate in your code before the insert, normalise case (lower(?)), convert booleans to 1 and 0, and make sure numbers reach SQLite as numbers, not words.

Find existing rows that break it

Before adding a rule or rebuilding a table, look for rows that would fail, with the same expression negated:

SELECT id, total, status FROM orders
WHERE NOT (total >= 0 AND status IN ('new', 'paid', 'shipped'));

Rows where the expression is NULL don’t show up, which matches how CHECK treats them. On SQLite 3.51.0, PRAGMA integrity_check also reports tables with rows that break their checks: CHECK constraint failed in orders.

Change the rule

SQLite can’t alter or drop a CHECK constraint in place. To change one, rebuild the table (the 12 steps in SQLite’s documentation): create the table with the new rule, copy the rows, drop the old table and rename the new one.

Don’t switch checks off to get past it

PRAGMA ignore_check_constraints = ON makes the connection skip CHECK rules. It’s meant for repairs and imports you clean up straight after; rows written while it’s on break the rule silently, and integrity_check reports them later.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, with the orders table above:

sqlite3 shop.db "INSERT INTO orders (customer_id, total) VALUES (1, -5);"

Error: stepping, CHECK constraint failed: total >= 0 (19)

The shell adds “Error: stepping,” and the result code; SQLite’s message is CHECK constraint failed: total >= 0. The other cases:

Error: stepping, CHECK constraint failed: status IN ('new','paid','shipped') (19)
Error: stepping, CHECK constraint failed: price_positive (19)
Error: stepping, CHECK constraint failed: ends > starts (19)
Error: stepping, CHECK constraint failed: age BETWEEN 0 AND 150 (19)
Error: stepping, CHECK constraint failed: on_sale IN (0, 1) (19)

Those are status = 'Paid', a price of 0 on a named constraint, a booking ending before it starts, 'forty' for an INTEGER age ('42' was stored as the integer 42), and 'true' for a flag. A NULL price passed. Adding rating INTEGER DEFAULT 0 CHECK (rating > 0) to a table with rows gave the message without the expression:

Error: stepping, CHECK constraint failed

With PRAGMA ignore_check_constraints = ON, the first insert succeeded, and afterwards PRAGMA integrity_check printed CHECK constraint failed in orders. Through Python’s sqlite3 module (SQLite 3.53.4), the error was sqlite3.IntegrityError with sqlite_errorname SQLITE_CONSTRAINT_CHECK.

In Inlet

Grid edits are staged until you commit (⌘S), and Review shows the exact SQL first, so you can check the values against the rule before they’re sent. If SQLite refuses a row, Inlet shows its message with the constraint’s name or expression.

CHECK constraint failed