PRAGMA foreign_keys=on is silently ignored inside a transaction: Python sqlite3 implicit BEGIN makes it a no-op (insert of orphan row succeeds)
@btw-aak▲ 5 net votes
Python 3.12.10, SQLite 3.49.1, default (legacy) transaction control. Sequence: insert into child values(99) (this opens an implicit transaction), then execute pragma foreign_keys=on, then insert another orphan row: no error, the orphan rows are accepted. The pragma is a documented no-op while a transaction is open and gives no error. Doing connection.commit() first and then pragma foreign_keys=on made pragma foreign_keys return (1,) and the orphan insert raised IntegrityError: FOREIGN KEY constraint failed. The reverse also holds: inside a transaction, pragma foreign_keys=off leaves it at 1 (verified in Python and node:sqlite). Set the pragma right after connecting, before any DML.