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

> 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.

Source: https://schemity.com/blog/sqlite-change-column-type-table-rebuild/

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:

```sql
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](https://www.sqlite.org/lang_altertable.html) lists what it does directly:

| Change | In place? | Since |
| --- | --- | --- |
| Rename a table or a column | Yes | 3.25.0 for columns |
| Add a column | Yes, with restrictions on defaults and constraints | 3.2.0 |
| Drop a column | Yes, unless it is in a primary key, unique constraint, index or foreign key, or used by a check, generated column, view or trigger | 3.35.0 |
| Set or drop `NOT NULL` | Yes, `ALTER TABLE ... ALTER COLUMN ... SET NOT NULL` | 3.53.0 |
| Add a `CHECK` constraint, or drop a named one | Yes, `ALTER TABLE ... ADD CONSTRAINT ... CHECK (...)` | 3.53.0 |
| Change a column's type | No, rebuild the table | |
| Add or change a foreign key, primary key or unique constraint | No, rebuild the table | |

The 3.53.0 [release notes](https://www.sqlite.org/changes.html) 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:

```sql
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](https://www.sqlite.org/foreignkeys.html) 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](https://www.sqlite.org/datatype3.html) 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:

```sql
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.

| Rebuild step | Written the usual way | Written the safe way |
| --- | --- | --- |
| `PRAGMA foreign_keys = OFF` | Inside the transaction, ignored | Before `BEGIN`, applied |
| Rows in `order_items` afterwards | 0 of 3 | 3 of 3 |
| Index and trigger on `orders` | Gone | Recreated from `sqlite_schema` |
| `'n/a'` in the `REAL` column | Copied as text, no error | Listed by the `typeof` check, so you can stop before the drop |

## 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](https://schemity.com/doc/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.

![Schemity's Impact drawer for changing orders.total from TEXT to REAL on a SQLite connection: under Loses data, Changes the type of orders.total, values may not survive the cast: ~3 rows, with Count exactly; under Rewrites the table, Rewrites orders to change total: ~3 rows; under Dependent objects, 2 objects depend on orders.total, trigger orders_touch depends on orders and view big_orders mentions orders in its body; the canvas shows orders with total as REAL, order_items and the big_orders view](https://schemity.com/images/blog/sqlite-change-column-type-table-rebuild/impact-sqlite-type-change.webp)

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](https://schemity.com/doc/migration-sql-diff/) shows every statement of the planned rebuild, the recreated trigger included, before you apply it.

![Schemity's change preview for changing orders.total from TEXT to REAL on SQLite: the planned migration runs PRAGMA foreign_keys=OFF, CREATE TABLE "orders__new" with "id" INTEGER PRIMARY KEY and "total" REAL NOT NULL, INSERT INTO "orders__new" SELECT from "orders", DROP TABLE "orders", ALTER TABLE "orders__new" RENAME TO "orders", CREATE INDEX "orders_updated_at_idx", CREATE TRIGGER orders_touch AFTER UPDATE OF total ON orders recreated with its body, PRAGMA foreign_key_check and PRAGMA foreign_keys=ON, followed by one planned change, two lint findings and the three impact findings](https://schemity.com/images/blog/sqlite-change-column-type-table-rebuild/migration-sqlite-rebuild.webp)

## 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](https://schemity.com/blog/postgres-add-not-null-column-large-table/). What `ON DELETE CASCADE` reaches, and why a diagram should show it, is in [ON DELETE CASCADE is invisible in your ERD](https://schemity.com/blog/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](https://schemity.com/blog/should-ai-agents-write-database-migrations/).

## Frequently asked questions

### Does SQLite's `ALTER TABLE` support changing a column's type?

No. SQLite's `ALTER TABLE` can rename a table or a column, add or drop a column, and since SQLite 3.53.0 set or drop `NOT NULL`, add a `CHECK` constraint and drop a named one, but it has no form that changes a column's type. The documented way is to create a new table with the new type, copy the rows, drop the old table and rename the new one.

### Why did my SQLite table rebuild delete rows from another table?

Because foreign keys were still on when the old table was dropped. With enforcement on, `DROP TABLE` first runs an implicit `DELETE`, which fires `ON DELETE CASCADE` on child tables. `PRAGMA foreign_keys = OFF` has no effect inside a transaction, so it has to run before `BEGIN`.

### Does a SQLite table rebuild keep triggers and indexes?

No. `DROP TABLE` removes every trigger and index attached to the table, and the new table starts without them. Save their SQL from `sqlite_schema` before the drop and run it again after the rename. Views survive, because they refer to the table by name and the rename restores it.