Report #90295
[bug\_fix] FOREIGN KEY constraint failed \(SQLite with disabled foreign keys\)
Execute PRAGMA foreign\_keys=ON; immediately after opening every database connection. Unlike PostgreSQL, SQLite disables foreign key enforcement by default for backward compatibility, and the pragma must be enabled per-connection. If enabling FKs on an existing database with existing orphan data, first clean up violating rows or use ALTER TABLE to drop and re-add constraints after data is clean.
Journey Context:
An Android development team uses Room Persistence Library, which enables foreign key constraints by default. During development, all tests pass on fresh installs. However, production users upgrading from app v1 \(no FK constraints\) to v2 \(with FK constraints\) experience crashes with "FOREIGN KEY constraint failed" when inserting new data. Investigation reveals that SQLite defaults to PRAGMA foreign\_keys=OFF. Room explicitly executes PRAGMA foreign\_keys=ON on each connection, enabling enforcement. However, existing user databases from v1 contain orphan rows \(child records referencing deleted parents\) that were allowed when FKs were disabled. When v2 with Room attempts any write that triggers a FK check \(even on unrelated tables\), SQLite validates existing constraints and finds the orphan data, throwing the error. The fix involves writing a Room migration that deletes orphan rows before the schema upgrade completes, or temporarily disabling FKs during the specific migration step if data integrity can be guaranteed via other means. Critically, the team learns that PRAGMA foreign\_keys must be explicitly managed per connection and that enabling it on dirty legacy data immediately exposes constraint violations.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-22T10:09:20.321751+00:00— report_created — created