SQLITE_CONSTRAINT_FOREIGNKEY: FOREIGN KEY constraint failed
A write would leave a child row pointing at a parent that doesn't exist, or delete a parent that still has children. Foreign keys must be switched on per connection.
- Result code
SQLITE_CONSTRAINT_FOREIGNKEY- Name
787- Category
- Extended result codes
What it means
SQLITE_CONSTRAINT_FOREIGNKEY (787) means a foreign key check failed. SQLite only enforces foreign keys on connections that run PRAGMA foreign_keys = ON; it is off by default for backward compatibility, which is why the same insert can succeed in one tool and fail in another.
The message doesn't say which key failed. PRAGMA foreign_key_check lists every violating row.
Common causes
- Inserting a child row whose parent id doesn't exist.
- Deleting or updating a parent row that still has children, without
ON DELETE CASCADEorSET NULL. - Loading tables in the wrong order: children before parents.
- Deferred constraints (
DEFERRABLE INITIALLY DEFERRED) failing atCOMMIT.
How to fix it
-
Find the rows that break a foreign key.
PRAGMA foreign_key_check; PRAGMA foreign_key_list(orders);
-
Insert parents before children, or delete children before parents.
-
For bulk loads, defer checking to the end of the transaction.
BEGIN; PRAGMA defer_foreign_keys = ON; -- load tables in any order COMMIT;
-
If deleting a parent should remove its children, declare the action on the foreign key.
CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE );
Reproduce it safely
PRAGMA foreign_keys = ON; CREATE TABLE p (id INTEGER PRIMARY KEY); CREATE TABLE c (pid INTEGER REFERENCES p(id)); INSERT INTO c VALUES (1); -- Runtime error: FOREIGN KEY constraint failed (19)
Questions
Why are foreign keys not enforced in my SQLite database?
Enforcement is per connection and off by default. Each connection has to run PRAGMA foreign_keys = ON, and it cannot be changed inside a transaction.
Related errors
SQLITE_CONSTRAINTconstraint failed23503PostgreSQL: foreign key constraint violation1452MySQL: Foreign key fails: cannot add child row1451MySQL: Foreign key fails: cannot delete parent row547SQL Server: Statement conflicted with a constraintORA-02291Oracle: integrity constraint violated - no parent keyORA-02292Oracle: integrity constraint violated - child record found
More extended result codes errors
SQLITE_ERROR_MISSING_COLLSEQno such collation sequenceSQLITE_BUSY_RECOVERYanother process is recovering the WALSQLITE_LOCKED_SHAREDCACHErecord locked in shared cacheSQLITE_CONSTRAINT_CHECKCHECK constraint failedSQLITE_ABORT_ROLLBACKstatement aborted by ROLLBACKSQLITE_BUSY_SNAPSHOTstale read can't become a writeSQLITE_IOERR_SHORT_READread returned fewer bytesSQLITE_CANTOPEN_ISDIRthe path is a directory
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.