Changing a Column Type in SQLite: The Table Rebuild and What It Deletes

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: SQLite cannot change a column's type in place, so you copy the table into a new one with the right type, drop the old one and rename. Done carelessly, that rebuild deletes child rows through ON DELETE CASCADE when PRAGMA foreign_keys = OFF is run inside the transaction, drops every trigger and index on the table, and leaves values that do not convert stored as text in the new numeric column.

SQLite cannot change a column's type with ALTER TABLE. The only way is a table rebuild: create a new table with the column typed the way you want, copy the rows across, drop the old table and rename the new one into its place. It takes four statements, and done in the obvious order it can delete rows from other tables, drop triggers and indexes, and leave some values unconverted without an error.

Every statement below was run with the sqlite3 shell that ships with macOS, Apple's own build of SQLite, which reports version 3.54.0 although the newest release on sqlite.org is 3.53.4, against an orders table whose total was created as TEXT and needs to become REAL, with an order_items child table, an index, a trigger and a view on it:

PRAGMA foreign_keys = ON;
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total TEXT NOT NULL,
    updated_at TEXT
);
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    sku TEXT NOT NULL
);
CREATE INDEX orders_updated_at_idx ON orders (updated_at);
CREATE TRIGGER orders_touch AFTER UPDATE OF total ON orders
BEGIN
    UPDATE orders SET updated_at = datetime('now') WHERE id = NEW.id;
END;
CREATE VIEW big_orders AS SELECT id, total FROM orders WHERE total > 100;
INSERT INTO orders (id, total) VALUES (1, '150.00'), (2, '19.90'), (3, 'n/a');
INSERT INTO order_items (order_id, sku) VALUES (1, 'A'), (1, 'B'), (2, 'C');

Which ALTER TABLE changes SQLite can do in place

Before rebuilding, check whether you need to. The SQLite ALTER TABLE documentation lists what it does directly:

The 3.53.0 release notes describe the newest addition as permitting "adding and removing NOT NULL and CHECK constraints". Both check the existing rows: SET NOT NULL on a column with a NULL failed with constraint failed, and so did adding CHECK (total > 0) to a table with a negative total. Anything older than 3.53.0, including the SQLite your driver may bundle, still needs a rebuild for those two.

Two things a SQLite table rebuild deletes, and one it gets wrong

Here is the rebuild as it is usually written, run in the same session as the setup so foreign keys are on, with the switch to turn them off placed inside the transaction:

BEGIN;
PRAGMA foreign_keys = OFF;
CREATE TABLE orders__new (id INTEGER PRIMARY KEY, total REAL NOT NULL, updated_at TEXT);
INSERT INTO orders__new SELECT id, total, updated_at FROM orders;
DROP TABLE orders;
ALTER TABLE orders__new RENAME TO orders;
COMMIT;

It succeeds, and afterwards SELECT count(*) FROM order_items returns 0. All three order items are gone.

Child rows, through the cascade. The foreign key documentation explains both halves. With enforcement on, DROP TABLE "performs an implicit DELETE to remove all rows from the table before dropping it", which "may invoke foreign key actions", and ON DELETE CASCADE is one. The PRAGMA that was meant to prevent it did nothing, because changing foreign key enforcement inside a transaction "does not return an error; it simply has no effect". Running PRAGMA foreign_keys; inside the transaction still printed 1. Without a cascade the same drop fails with FOREIGN KEY constraint failed instead, which is the safer way to find out.

Every trigger and index on the table. After the rebuild, sqlite_schema lists the view and both tables, and no longer lists orders_touch or orders_updated_at_idx. DROP TABLE removes what is attached to the table, and nothing in the rebuild puts them back. The missing index shows up as a slow query. The missing trigger shows up as an updated_at that stops changing.

Values that do not convert. The copy did not fail on 'n/a'. 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". The new column holds 150.0 and 19.9 as real numbers and 'n/a' as text. Before the rebuild, big_orders compared text with text and returned all three orders, 19.90 included. After it, 19.90 correctly drops out, but order 3 stays in the view as a big order, because SQLite sorts any text above any number. A table declared STRICT would have refused the value with cannot store TEXT value in REAL column.

How do you change a column type in SQLite?

Follow the 12-step procedure from the ALTER TABLE documentation. For this table it is:

PRAGMA foreign_keys = OFF;
BEGIN;
SELECT type, name, sql FROM sqlite_schema WHERE tbl_name = 'orders' AND type <> 'table';
CREATE TABLE orders__new (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL,
    updated_at TEXT
);
INSERT INTO orders__new (id, total, updated_at) SELECT id, total, updated_at FROM orders;
SELECT id, total FROM orders__new WHERE typeof(total) <> 'real';
-- stop here if that returned rows: ROLLBACK, fix them, start again
DROP TABLE orders;
ALTER TABLE orders__new RENAME TO orders;
CREATE INDEX orders_updated_at_idx ON orders (updated_at);
CREATE TRIGGER orders_touch AFTER UPDATE OF total ON orders
BEGIN
    UPDATE orders SET updated_at = datetime('now') WHERE id = NEW.id;
END;
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys = ON;

What each addition does:

• PRAGMA foreign_keys = OFF comes before BEGIN, so it takes effect and the drop cascades into nothing. All three order items survived.
• The first SELECT prints the CREATE INDEX and CREATE TRIGGER statements to run again after the rename.
• The typeof check lists rows whose value did not convert. Here it returned 3|n/a. A script pasted in one go does not stop there, so run the steps one at a time, and if the check returns anything, ROLLBACK, fix those rows, and start again.
• PRAGMA foreign_key_check lists rows that break a foreign key. With enforcement off, nothing else would.

The view needed no attention. It names orders, and after the rename orders exists again. A view that selects a column you renamed or dropped does need recreating, as step 9 of the documented procedure says.

How Schemity plans a SQLite rebuild

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. On a SQLite connection it alters directly for adding, renaming and dropping columns, and plans a rebuild for the rest: a type, nullability or default change, and adding or dropping a primary key, unique, check or foreign key constraint. That includes the NOT NULL and CHECK changes SQLite 3.53 can make in place.

Its rebuild follows the documented order. PRAGMA foreign_keys = OFF runs on the connection before the transaction starts, PRAGMA foreign_key_check runs before the commit and rolls the whole migration back if any row breaks a key, and foreign keys are switched back on whether the migration succeeded or not. The rebuilt table gets its indexes and its triggers back, keeps AUTOINCREMENT only if the table was declared with it, and computes generated columns again rather than copying them, since SQLite refuses an insert into one. To see the impact of every change before it runs, impact analysis reports this one as "Changes the type of orders.total, values may not survive the cast", which is the 'n/a' case, and "Rewrites orders to change total", with a row count when the database has been analysed with ANALYZE. It also lists orders_touch as a trigger that depends on orders, and big_orders as a view that mentions it.

The triggers come back from their own SQL, as read from sqlite_schema, the same way the script above recreates orders_touch. SQLite does not check a trigger's body when the trigger is created, so a trigger that names a column the same change renames or drops would be created and then fail when it fires. Schemity refuses that save instead, naming the table and the column, so the trigger can be changed first. The migration SQL diff shows every statement of the planned rebuild, the recreated trigger included, before you apply it.

What to check before any SQLite rebuild

• Your SQLite version: on 3.53.0 or later, NOT NULL and CHECK changes no longer need a rebuild.
• PRAGMA foreign_keys = OFF before BEGIN, never after it.
• The table's triggers and indexes, saved from sqlite_schema and recreated after the rename.
• A typeof check on the new table before the old one is dropped.
• PRAGMA foreign_key_check before COMMIT.

Schema changes on PostgreSQL have their own traps, and the ones that lock or rewrite a large table are in adding a NOT NULL column to a large Postgres table. What ON DELETE CASCADE reaches, and why a diagram should show it, is in ON DELETE CASCADE is invisible in your ERD. For a migration written by an ORM or an AI agent rather than by hand, see should AI agents write database migrations.