Mechanism◆Article

Because PRAGMA foreign_keys cannot be changed inside an open transaction, dropping the old table as part of table.transform() could previously fire destructive ON DELETE actions and silently delete or modify rows that referenced that table.

The bug arose because the foreign_keys pragma can't be toggled mid-transaction, so the table drop inside transform() could unintentionally cascade-delete or modify referencing rows. ✦ AI generated

Simon Willison · Simon Willison's Weblog · 2026-07-12 · original ↗

Share card ↓Export .md Export .txt

The pragma cannot be changed inside a transaction, so previously dropping the old table as part of the transform could fire those actions and silently delete or modify referencing rows.

Read full article ↗excerpt · fair-use quotation

✳ PRAGMA foreign_keys ✳ sqlite-utils

Around this claim

Context · 1

table.transform() implements the SQLite-documentation-recommended pattern for altering tables beyond what ALTER TABLE supports: create a new temp table with the new schema, copy the data across, then drop the old table and rename the new one into place.Simon Willison · Simon Willison's Weblog · conf 60%

This moment responds to

explains mechanism → table.transform() now raises a TransactionError if it's called inside an open transaction with PRAGMA foreign_keys enabled when the table is referenced by foreign keys with destructive ON DELETE actions (CASCADE, SET NULL, or SET DEFAULT).Simon Willison · Simon Willison's Weblog