[pstuart]

You are probably correct, but I imagine the SQLite team's dedication to backwards compatibility has things the way they are so that existing systems can user later versions a swap without worrying about changing the SQL using it.

[justinsaccount]

The entire point of "SQLite should have editions" is so that projects can opt into a set of modern defaults for 2026 and not get all of those backwards compatible decisions from 20 years ago.

[usefulcat]

If they’re opt-in, how could the new defaults be a problem for backwards compatibility?

[dspillett]

GPP's wording suggests that the defaults simply be changed to what is being discussed as more generally sensible in current times, rather than being opt-in.

Given how many projects are potentially out there effectively relying on the current settings, and SQLite's general attitude to backwards compatibility, that would likely not be considered a good idea. Opting in with an edition flag for new (or updating) projects does seem like a good solution to this to serve all of old, active, and new projects, but it would increase potential bug surface area and therefor testing requirements, and the existing setting do allow all that to be opted in/out to/from already (and it is only four settings we are talking about here).

That FKs being enforced is set per-connection rather than at the database level is something that surprised me a lot when I found out. A way of setting that at DB creation (or via ALTER DATABASE after) seems like quite an omission because if you have multiple potential routes that can update the same DB any one of them could cause serious the others will encounter.