SQLite: FOREIGN KEY constraint failed (tested)

sqlite3.IntegrityError: FOREIGN KEY constraint failed only happens if foreign keys are actually being enforced. We measured SQLite's biggest surprise here: they're off by default.

1. Off by default: a missing parent row doesn't even error

con.execute("PRAGMA foreign_keys")  # (0,) — OFF by default
con.execute("INSERT INTO child (id, parent_id) VALUES (1, 999)")  # parent 999 doesn't exist
con.commit()
# no error at all

Measured: unlike Postgres and MySQL (InnoDB), where foreign keys are always enforced, SQLite leaves them off by default even if the column is declared REFERENCES parent(id). The constraint exists in the schema but isn't checked.

2. Turning it on, then hitting the real error

con.execute("PRAGMA foreign_keys = ON")
con.execute("INSERT INTO child (id, parent_id) VALUES (2, 999)")
sqlite3.IntegrityError: FOREIGN KEY constraint failed

Notably, the message names no table or column — a contrast with every other constraint error in this series (NOT NULL, UNIQUE, CHECK all name the specific column). With foreign keys on, deleting a parent row that still has children fails the same way:

con.execute("DELETE FROM parent WHERE id=1")  # child row still references it
sqlite3.IntegrityError: FOREIGN KEY constraint failed

3. The fix: turn it on every connection, deliberately

PRAGMA foreign_keys = ON is per-connection, not persisted in the database file — we confirmed a fresh connection to the same file defaults back to off. Run it right after every connect() call if you want enforcement. For the delete case, either remove/reassign the children first, or declare ON DELETE CASCADE/ON DELETE SET NULL on the foreign key so SQLite handles it automatically.

Frequently asked questions

Are foreign keys enforced by default in SQLite?

No — we measured PRAGMA foreign_keys returning 0 (off) by default, and confirmed an insert referencing a nonexistent parent row succeeds with zero error in that state, even though the column is declared REFERENCES.

Does the error message name the table or column involved?

No — we confirmed it's just the generic text 'FOREIGN KEY constraint failed' with no specifics, unlike NOT NULL/UNIQUE/CHECK errors in this series which all name the exact column.

Is PRAGMA foreign_keys = ON permanent once set?

No — we confirmed it's per-connection and not stored in the database file; a new connection to the same file defaults back to off, so it needs to be set every time you connect if you want enforcement.

What triggers this error on DELETE instead of INSERT?

Deleting a parent row that a child row still references — we reproduced this directly: with foreign_keys on, deleting the referenced parent failed identically to the missing-parent insert case.

How do I delete a parent without this error?

Either delete or reassign the referencing child rows first, or declare ON DELETE CASCADE (auto-delete children) or ON DELETE SET NULL (auto-null the reference) on the foreign key definition — not directly tested here, per SQLite's own documented behavior.

Written with AI assistance. Measured on 2026-10-03 with Python 3.13.5's built-in sqlite3 module, checking the default state of PRAGMA foreign_keys, an insert referencing a missing parent row with it on, and a delete of a parent that still has children. Every result below is copy-pasted output, not paraphrased. PlainHeaders is our extension; Python's sqlite3 is built in.