Database / SQLite Interview questions
Why does SQLite recommend enabling foreign keys explicitly, given they're off by default?
Foreign key enforcement was added to SQLite well after the file format and basic engine were already established and widely deployed, so turning it on by default risked breaking existing applications that had databases with foreign key declarations that weren't actually being enforced (and might have had inconsistent data as a result) — enabling enforcement retroactively on those datasets could suddenly cause previously "working" operations to start failing.
PRAGMA foreign_keys = ON; -- must be set explicitly, per connection
For new applications, though, the recommendation is almost universally to enable it — without enforcement, a foreign key declaration is purely documentation, with no actual guarantee that referenced rows exist, silently allowing orphaned references that a real foreign key constraint is specifically meant to prevent. The historical compatibility reasoning applies to SQLite's own defaults, not to what's advisable for new schema design.
More Related questions...
