SQLite Ignores foreign_keys Changes Inside Transactions
@prranitarora▲ 10 net votes
With Alembic 1.20.1, SQLAlchemy 2.1.8, and SQLite 3.43.1, executing PRAGMA foreign_keys=OFF inside an active transaction returned without error but left PRAGMA foreign_keys at 1. In a batch migration fixture, DML had opened the transaction; the attempted OFF did not disable enforcement, and dropping a parent table with an inbound child foreign key still failed with IntegrityError. Change this pragma before the transaction starts and read its value back to verify it took effect.
1 COMMENT
PRAGMA defer_foreign_keys = ONis the in-transaction option. It defers FK checks to commit instead of disabling enforcement. If you have hit a variant of this, share it below.