SQLite Forum

Why is foreign key support based on the connection?

Why is foreign key support based on the connection?

(1) By Cubi (Cubi73) on 2020-04-14 13:37:47 [link] [source]

According to the section "Enabling Foreign Key Support" in the documentation the support for foreign key constraints must be enabled manually for each database connection via

PRAGMA foreign_keys = ON;

Though I understand the reason behind disabling this feature by default, I do not understand why it is implemented the way it is. More precisely, I am wondering why support for foreign key constraints is an option for the database connection and not an option for the database file. If I create a database which relies on foreign key constraints, shouldn't this feature be enabled for each and every connection to that database? I am having trouble coming up with a scenario where having support for foreign key constraints as an option for the connection is of any use.

Maybe someone can share some insights on this subject. Why is this option based on the connection? And are there any "reasonable" scenarios where it is useful to allow one connection to insert ghost references while disallowing this for other connections?

(2) By Simon Slavin (slavin) on 2020-04-14 15:59:49 in reply to 1 [link] [source]

These all contribute:

FOREIGN KEYs were introduced long after the database format was designed. So there's no obvious place in the database file format to store whether they're ON or OFF.

Historical reasons. This is how it used to be done. Changing that would require people to rewrite their software.

A new error message which old software didn't handle, because it didn't exist. Suppose you are running a general database tool which allows you to view databases, insert and delete data, etc.. And it was written before FOREIGN KEYs were implemented. You try to delete a row (which happens to be a parent row with RESTRICT set) and the SQLite API returns 787. But the software doesn't know what that error is or how to handle it because it was written before error code 787 existed. So it just reports "Unknown error 787." to you. And you don't know what that means either, because you're a user not a programmer.

If FOREIGN KEYs had been implemented when the database format was devised the problem would not exist. Unfortunately they hadn't, so now we have to handle the more complicated case.

(4) By Cubi (Cubi73) on 2020-04-14 18:44:49 in reply to 2 [link] [source]

I can see how your first problem contributes to not having this option stored in the database file, but problems two and three should have already been taken care of by the fact that support for foreign key constraints is disabled by default, right? And introducing changes to the database file format (like whether or not support is enabled) while leaving the default behavior unchanged shouldn't be of great concern either, because

there have been enhancements to SQLite such that newer database files are unreadable by older versions of the SQLite library. (File Format Changes in SQLite)

And I do not see any major problem adding this option to the file format while leaving the foreign_keys pragma intact. The stored option could be implemented as shown in the diagram below.

                        defaults to                   defaults to
 PRAGMA foreign_keys  --------------> stored option --------------> support disabled

/ can be changed for \             / is set at data- \
\  every connection  /             \  base creation  /

which wouldn't break existing code (at least I don't see how it could break existing code) and would allow future code to just enable support for foreign key constraints for all connections.

(Just to be sure, this isn't a feature request. I'm fine with putting pragmas all over my code. I was just curious why it is the way it is :)

(6) By Keith Medcalf (kmedcalf) on 2020-04-14 21:18:10 in reply to 4 [link] [source]

You can change the default behaviour by compiling the core library with the compile time constant SQLITE_DEFAULT_FOREIGN_KEYS defined as 1 rather than 0, which will change the default pragma foreign_keys setting to be 1 for every connection, rather than disabled.

This change applies to the application using that particular SQLite3 core library and to every connection it opens irrespective of the database file.

Generally speaking, I would expect that if one has put integrity constraints in the database via the mechanism of foreign key constraints, then I would expect that one would expect them to be respected.

The default setting is 0 because for a long time SQLite3 allowed prayerful integrity constraints, meaning that they could be included but could not be enforced -- and for historic compatibility the enforcement defaults to disabled.

This does not preclude you from changing the default, however.

(3) By David Jones (vman59) on 2020-04-14 16:00:04 in reply to 1 [source]

If you have a bulk update that's already filtering by the referenced table, extra checks of the foreign key constraints are needless overhead. I don't think it's about allowing you to knowingly/willingly violate the constraint.

(5) By Cubi (Cubi73) on 2020-04-14 18:53:05 in reply to 3 [link] [source]

Now that you say this, it is blindingly obvious. Usually I have to deal with just a few small transactions, where this is something I wouldn't even think about. Thanks for your answer :)