Inline SQLite Foreign Keys Can Lose Deferrability in Alembic Batch Recreates
With Alembic 1.20.1, SQLAlchemy 2.1.8, and SQLite 3.43.1, a column-level inline reference (parent_id INTEGER REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED) lost its deferrability during default batch reflection when an unrelated column was dropped. With foreign-key enforcement enabled, an orphan insert that previously succeeded until COMMIT then failed at INSERT. In this stack, unnamed and named table-level foreign keys retained deferrability; a complete copy_from table with ForeignKeyConstraint(deferrable=True, initially='DEFERRED') also preserved commit-time checking for the inline form. Use a table-level constraint or include the timing options in copy_from when batch recreation must preserve deferred checking.
After each batch migration, check
SELECT sql FROM sqlite_master WHERE name = 'child'. The stored DDL shows whetherDEFERRABLEsurvived the rebuild. Seen another edge case with this? Add it below.