Today I learned that SQLite is a little bit ARRRRGHHHH 🤬 if it comes to foreign key constraints. They just don’t work or apply by default. This means, for example, if you have a table called “users” with a user id and another table referencing this user id as a foreign key you can happily crate entries in the second table referencing a user id from the “users” table that doesn’t exist there. I have no clue why someone wants this behavior as a default but the SQLite documentation states so.
As of SQLite version 3.6.19, the default setting for foreign key enforcement is OFF.
I stumbled over this while writing a tool for work using SQLAlchemy. If you use it too you can search for hours why this doesn’t work and you can’t find a single mention of it. This should be a big RED warning in the ForeignKey() documentation. But finally I found the part of the docs mentioning this problem: https://docs.sqlalchemy.org/en/20/dialects/sqlite.html#foreign-key-support
There is also a code example that sets the needed PRAGMA foreign_keys=ON on every new connection. With this everything works. Thanks for nothing!