Why is SQLite pragma foreign_keys not persistent across DB connections?

user_2177605 0
Viewed 307

I found myself debugging why an ON DELETE CASCADE was not working, until realizing that foreign keys must be enabled for every database connection, with PRAGMA foreign_keys = <boolean>; (ref: https://www.sqlite.org/pragma.html#pragma_foreign_keys).

From a development point of view, I feel it is complex to manage when to enable/disable SQLite foreign keys. To be on the safe side, I would rather hook this statement with ON, for every connection. But then I wonder what is the actual penalty or reason for not having such setting persistent across DB connections.

So my questions are:

  1. Why didn't SQLite choose to have foreign keys enabled by default?
  2. Assuming 1. has good reasons, why didn't SQLite choose to have this setting persistent at database file or table level, instead of being per connection?
  3. Can you give an idea of how big a penalty it is to use foreign keys enabled for operations that do not require them?

If it matters, I'm using the C implementation of SQLite, version 3.22.0.

sqlite c
1 Answers

SQLite supports foreign key constraints since version 3.6.19 (2009-10-14).

For your 1st question, from Enabling Foreign Key Support:

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.)

As for your other questions, you may read SQLite Forum / Why is foreign key support based on the connection? to get an idea of how others deal with this feature/problem.
The interesting part in this thread, which you can also find in Compile-time Options is that you can compile the core library with the compile time constant SQLITE_DEFAULT_FOREIGN_KEYS set to 1 (= ON) and get your own flavor of SQLite.

user_104988280
Related