Build1 publisher2 min readPublished
SQLite's usual column-type rebuild deletes child rows through ON DELETE CASCADE
Schemity's developer found SQLite's usual column-type rebuild deleted all three child rows because its PRAGMA ran inside the transaction. A safe version sets the foreign-key switch before BEGIN and checks the copied values before dropping the old table.
The Engineer · Build desk
Drafted by a language model from the sources cited here and checked against its claim ledger before publication. How we use AISend a correction
What happened
- Inside the transaction, PRAGMA foreign_keys still printed 1, and SQLite's docs say a change made there does not return an error and simply has no effect.
- On a child key without a cascade, the same DROP TABLE fails with FOREIGN KEY constraint failed instead of deleting the child rows.
- After the rebuild, sqlite_schema no longer listed the orders_touch trigger or the orders_updated_at_idx index, and none of the four statements recreates them.
- The value 'n/a' copied into the new REAL column without an error and stayed stored as text beside 150.0 and 19.9.
- SQLite 3.53.0 added ALTER TABLE support for adding and removing NOT NULL and CHECK constraints, and both reject tables whose existing rows violate them.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- exposure A migration written in the common order commits successfully on a cascading schema, so the lost child data surfaces only when someone counts the child table.
- cost Teams pay for the dropped index and trigger after the migration, in slow queries and stale updated_at values, because neither loss raises an error when it happens.
- constraint Declaring the new column REAL validates nothing, so a type-change rebuild has to carry its own query for values left as text before the old table is dropped.
- decision Projects on a SQLite older than 3.53.0, including the copy a driver bundles, still need the full rebuild to add NOT NULL or CHECK, so the bundled version decides how risky that migration is.
The deletion needs three conditions in one session: foreign key enforcement on, a child key declared ON DELETE CASCADE, and the off switch placed after BEGIN [6][9]. The test had all three, with PRAGMA foreign_keys = ON in its setup and order_items pointing at orders through a cascade [16][5]. SQLite's foreign key documentation says DROP TABLE "performs an implicit DELETE to remove all rows from the table before dropping it," and that delete "may invoke foreign key actions" [7]. The cascade emptied order_items, and the transaction still committed [6].
The post comes from the blog of Schemity, a desktop ERD tool its author builds [3]. The author wrote that the hard failure on a child key without a cascade "is the safer way to find out" [9]. I agree. The error stops the migration before any row is gone.
The documented behaviour sets the order of a rebuild that avoids all three losses:
1. Run PRAGMA foreign_keys = OFF before BEGIN, the only place it takes effect [1]. 2. Inside the transaction, create orders__new and copy the rows, the first two of the four statements [1][2]. 3. Query the new column for values still stored as text, and fix them or roll back while the old table still exists [3]. 4. Save the index and trigger definitions, drop the old table, rename the new one, then recreate both objects [2]. 5. Commit, then switch enforcement back on, since that change is also ignored inside a transaction [1].
Step 3 exists because REAL in SQLite is a type affinity, not a check, and the datatype documentation says a text value that is not a well-formed number "is stored as TEXT" [17]. That changes query results. Before the rebuild, the big_orders view compared text with text and returned all three orders, 19.90 included [13]. After it, 19.90 dropped out as it should. But 'n/a' kept order 3 in the view as a big order, because SQLite sorts any text above any number [13].
The tests ran on the sqlite3 shell that ships with macOS, Apple's own build, which reports version 3.54.0 while the newest release on sqlite.org is 3.53.4 [4]. Apple's shell is, by its own account, a release ahead of the project [4]. Changing a column's type needs the rebuild on any version, because ALTER TABLE cannot do it [1]. The 3.53.0 constraint statements are careful work. They check existing rows and refuse the change when a row violates the new constraint [14]. The REAL column in a rebuild does no such checking [17].
What to watch
- Whether migration tools that generate SQLite table rebuilds emit the foreign-key PRAGMA before BEGIN or inside the transaction.
- A SQLite release that lets ALTER TABLE change a column's type in place would remove the rebuild for this case.
- Whether Apple's macOS build reporting 3.54.0 behaves the same as sqlite.org's 3.53.4 release on these tests.