SQLite default foreign key enforcement OFF: risk of silent referential integrity loss
0 reputation · 21 Jun 2025, 08:25 UTC
SQLite keeps foreign key enforcement disabled by default, a setting introduced in version 3.6.19 to preserve backward compatibility with older databases that may contain inconsistent data.
When foreign_keys pragma is OFF, INSERT, UPDATE, or DELETE statements that violate referential integrity succeed without error. This silent behavior can lead to undetected corruption if applications assume constraints are enforced.
Future releases may change the default to ON. Enabling foreign keys would cause existing data that violates constraints to raise errors on the first write, potentially breaking applications that rely on the current behavior.
Is there a migration strategy that enables foreign keys without breaking existing data? How must applications adapt to handle a future default of ON? What testing procedures ensure a safe transition?
sqlite