FOREIGN KEY constraint failed

What it means

A foreign key says a column in one table (the child, albums.artist_id) must match a row in another (the parent, artists.id). Your statement would have broken that: a child pointing at a parent that doesn’t exist, or a parent removed or renumbered while children still point at it.

Unlike most databases, SQLite doesn’t say which table, column or row. The message is always the same, with the result code SQLITE_CONSTRAINT (19), extended code SQLITE_CONSTRAINT_FOREIGNKEY (787).

Foreign keys are off by default. SQLite only enforces them on a connection that has run PRAGMA foreign_keys = ON, and the setting lasts only for that connection. That’s why the same statement can work in one tool and fail in your app (or the other way round), and why a database with foreign keys declared can still contain rows that break them.

Common causes

1. A child row with a parent id that doesn’t exist: a wrong id, or the child inserted before its parent.
2. Deleting a parent that still has children, when the foreign key has no ON DELETE CASCADE or ON DELETE SET NULL.
3. Changing a parent’s key that children use.
4. Dropping a parent table. DROP TABLE deletes its rows first, and that fails while children refer to them.
5. A deferred foreign key at COMMIT. Constraints declared DEFERRABLE INITIALLY DEFERRED are checked when the transaction commits, so the error comes from COMMIT, not from the statement that caused it.

How to fix it

Find the missing or blocking row

For an insert or update, check that the parent exists:

SELECT id FROM artists WHERE id = 98;

For a delete, find the children that still refer to the parent:

SELECT id, title FROM albums WHERE artist_id = 1;

To list every row that breaks a foreign key, including ones written while enforcement was off:

PRAGMA foreign_key_check;

albums|2|artists|0

Each line is the child table, the child’s rowid, the parent table, and the foreign key’s number.

Insert parents first, or defer the check

Insert the parent before the child, and delete children before their parent. When rows refer to each other or arrive in any order (an import, a copy between databases), defer the check to the end of the transaction:

BEGIN;
PRAGMA defer_foreign_keys = ON;
INSERT INTO albums VALUES (60, 'Later', 77);       -- parent 77 doesn't exist yet
INSERT INTO artists VALUES (77, 'Late artist');
COMMIT;

defer_foreign_keys turns itself off when the transaction ends. If a row is still missing at COMMIT, the commit fails and the transaction stays open, so you can fix the data and commit again.

Decide what deleting a parent should do

If children should go with their parent, or lose the link, say so in the table definition:

artist_id INTEGER REFERENCES artists (id) ON DELETE CASCADE

SQLite’s ALTER TABLE can’t change an existing foreign key. You rebuild the table: create a new one with the right definition, copy the rows across, drop the old one and rename the new one, as described in SQLite’s ALTER TABLE documentation.

Turn enforcement on, at the right moment

Run this right after opening each connection, outside any transaction:

PRAGMA foreign_keys = ON;

Inside a transaction it does nothing and reports no error. Check it took effect with PRAGMA foreign_keys;, which returns 1. When you first turn it on for an old database, run PRAGMA foreign_key_check; to find the rows that were written while it was off.

“foreign key mismatch” is a different problem

If the message is foreign key mismatch - "c3" referencing "p3", the foreign key itself is broken: the parent column isn’t a primary key or unique, or doesn’t exist. Add a unique constraint or index to the parent column, or point the foreign key at the right column.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, with artists (id, name) and albums (id, title, artist_id REFERENCES artists (id)), and one artist with one album. A new connection has enforcement off, so an orphan goes in without complaint:

sqlite3 fk.db "PRAGMA foreign_keys;"
sqlite3 fk.db "INSERT INTO albums VALUES (2, 'Ghost', 99); SELECT changes();"

0
1

With enforcement on:

sqlite3 fk.db "PRAGMA foreign_keys = ON; INSERT INTO albums VALUES (3, 'Ghost 2', 98);"

Error: stepping, FOREIGN KEY constraint failed (19)

The shell adds “Error: stepping,” and the result code; SQLite’s message is FOREIGN KEY constraint failed. Deleting the artist, changing its id and DROP TABLE artists failed with the same message. Turning enforcement on inside a transaction was ignored:

sqlite3 fk.db "BEGIN; PRAGMA foreign_keys = ON; PRAGMA foreign_keys; COMMIT;"

0

A deferred foreign key failed at COMMIT (line 5 of the script), and succeeded once the parent was inserted and the transaction committed again:

Runtime error near line 5: FOREIGN KEY constraint failed (19)

In Inlet

Inlet shows SQLite’s message when a statement or a commit fails. Run PRAGMA foreign_keys; in the query editor to see whether the connection enforces foreign keys, and PRAGMA foreign_key_check; to list orphan rows. Grid edits are staged until you commit (⌘S), and Review shows the exact SQL first, so you can check the order of inserts and deletes before they run.