SQLITE_CONSTRAINT: constraint failed
An INSERT or UPDATE broke a constraint: UNIQUE, PRIMARY KEY, NOT NULL, CHECK or FOREIGN KEY. The message and extended code name which one.
- Result code
SQLITE_CONSTRAINT- Name
19- Category
- Primary result codes
What it means
SQLITE_CONSTRAINT (19) means a statement would have broken one of the table's rules, so SQLite stopped it. The generic text is constraint failed, but the message usually names the rule and column, such as UNIQUE constraint failed: users.email, and the extended code tells you the kind: SQLITE_CONSTRAINT_UNIQUE, _PRIMARYKEY, _NOTNULL, _CHECK, _FOREIGNKEY, _DATATYPE (STRICT tables) or _TRIGGER (a RAISE() in a trigger).
By default the failing statement is undone (conflict mode ABORT) but earlier statements in the same transaction are kept.
Common causes
- A duplicate value in a primary key or unique column.
- A NULL in a
NOT NULLcolumn, often because the insert left the column out and it has no default. - A value that fails a
CHECKexpression. - A child row whose parent doesn't exist, with
PRAGMA foreign_keys = ON. - A value of the wrong type in a
STRICTtable.
How to fix it
-
Read the message or the extended code to see which constraint failed, then look at the table definition.
SELECT sql FROM sqlite_schema WHERE name = 'users';
-
For duplicates you want to merge or skip, use an upsert or a conflict clause.
INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada') ON CONFLICT (email) DO UPDATE SET name = excluded.name; -
For the other constraints, fix the data you are writing: supply the missing value, insert the parent row first, or correct the value that fails the check.
Related errors
SQLITE_CONSTRAINT_UNIQUEUNIQUE constraint failedSQLITE_CONSTRAINT_PRIMARYKEYPRIMARY KEY constraint failedSQLITE_CONSTRAINT_NOTNULLNOT NULL constraint failedSQLITE_CONSTRAINT_CHECKCHECK constraint failedSQLITE_CONSTRAINT_FOREIGNKEYFOREIGN KEY constraint failedSQLITE_CONSTRAINT_DATATYPEvalue doesn't fit a STRICT column
More primary result codes errors
SQLITE_ERRORSQL logic errorSQLITE_PERMaccess permission deniedSQLITE_ABORTquery abortedSQLITE_BUSYdatabase is lockedSQLITE_LOCKEDdatabase table is lockedSQLITE_NOMEMout of memorySQLITE_READONLYattempt to write a readonly databaseSQLITE_INTERRUPTinterrupted
The code and message text are from the SQLite result and error codes documentation. Browse the full SQLite error list or search every database error.
Work on SQLite in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQLite and the other databases you use.