It's not possible to modify the PRAGMA foreign keys / defer foreign keys if you are inside a transaction, which could result in cascade deletes firing and deleting data if transform() is used inside a db.atomic().
Experiment here: https://claude.ai/share/564b187d-d126-47ea-9b59-07c16ade0b70
Key observation from Claude Fable 5The pragma guard exists and even handles the in-transaction case with defer_foreign_keys. Let me view the full block:
Short answer: no — transform() explicitly defends against exactly this. But I found one edge case where it does wipe child rows.
Tested on the 4.1 release (SQLite 3.45). The generated SQL is the classic 4-step rebuild: CREATE new table with STRICT → INSERT ... SELECT (rowids preserved) → DROP TABLE old → RENAME. The instinct behind the question is correct: with PRAGMA foreign_keys=ON, DROP TABLE performs an implicit DELETE FROM, which does fire ON DELETE CASCADE.
But transform() guards against it (db.py, lines 2559–2593): if the pragma is on, it flips foreign_keys=0, runs the rebuild in a transaction, runs PRAGMA foreign_key_check before committing (raises and rolls back on violations), then restores the pragma.
Experiment — authors ← books with ON DELETE CASCADE, 2 authors / 3 books:
- FKs ON, transform parent to strict: books 3 → 3. Table is
STRICT, FK check clean, and the cascade still functions afterward — deleting an author removes their books, since the child's FK references the table by name and the rename slots the new table in. - Control — same 4 statements by hand, no pragma guard: books 3 → 0. So the trick alone is genuinely dangerous; the guard is what saves you.
- Transforming the child instead: the rebuilt schema keeps
ON DELETE CASCADE. That's new-ish — before 4.0, transform silently dropped FK actions (issue add ability to configure "on delete" and "on update" attributes of foreign keys: #530).
The footgun: call transform() with FKs on inside an already-open transaction (db.begin() or with db.atomic():). The pragma is a no-op mid-transaction, so sqlite-utils falls back to defer_foreign_keys=ON — which defers violation checks, not cascade actions. The DROP cascaded and books went 3 → 0 silently; the post-check passes because the wiped state is self-consistent. Nothing in the docs mentions this (defer_foreign_keys greps to zero there), so it might be worth filing.
One last note: plain sqlite3/sqlite-utils connections default to FKs off, so most users never even engage the pragma dance in the first place.