skip to content

A SQLite migration must change a column's type and add a UNIQUE constraint; what can ALTER TABLE do directly, and how do you do the rest safely?

level: seniorimportance: should knowfreq 45%

answer

  1. the schema is stored as text
  2. rename, add, drop, and little else
  3. 3.53.0 added constraint edits
  4. build new, copy, drop, rename
  5. foreign keys off before BEGIN

basics

~20 s

SQLite's ALTER TABLE can rename tables and columns, add and drop columns and, since 3.53.0, add or drop NOT NULL and CHECK constraints. Changing a type or adding UNIQUE needs the rebuild: create the new table, copy, drop the old, rename.

solid answer

~50 s

SQLite keeps its schema as the original `CREATE` text, so `ALTER TABLE` supports only edits it can make to that text: `RENAME TO`, `RENAME COLUMN`, `ADD COLUMN` (with restrictions, such as no UNIQUE or PRIMARY KEY), `DROP COLUMN` (refused if the column is indexed, constrained or referenced), and since 3.53.0 `ALTER COLUMN ... SET NOT NULL` / `DROP NOT NULL`, `ADD CHECK` and `DROP CONSTRAINT`. Changing a type or adding UNIQUE uses the documented rebuild: `PRAGMA foreign_keys=OFF` outside any transaction, `BEGIN`, save the table's index, trigger and view SQL, `CREATE TABLE new_X`, `INSERT INTO new_X SELECT ...`, `DROP TABLE X`, `ALTER TABLE new_X RENAME TO X`, recreate the saved objects, `PRAGMA foreign_key_check`, `COMMIT`, then foreign keys back on. Check for duplicates and unconvertible values first, and remember that a phone's built-in SQLite may predate 3.53.0.

code

sql · 16 lines
sql
PRAGMA foreign_keys=OFF;
BEGIN;
SELECT type, sql FROM sqlite_schema WHERE tbl_name='products';
CREATE TABLE new_products(
  id    INTEGER PRIMARY KEY,
  sku   TEXT NOT NULL UNIQUE,
  price REAL
);
INSERT INTO new_products(id, sku, price)
  SELECT id, sku, CAST(price AS REAL) FROM products;
DROP TABLE products;
ALTER TABLE new_products RENAME TO products;
-- recreate the indexes, triggers and views saved above
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys=ON;

go deeper

for a junior

Recall that SQLite's ALTER TABLE renames tables and columns and adds or drops columns, and that most other changes need a new table.

for a middle

List the ADD COLUMN and DROP COLUMN restrictions, the 3.53.0 constraint forms, and why the schema-as-text design limits ALTER TABLE.

for a senior

Run the rebuild in the documented order, explain the foreign_keys and rename traps, pre-check duplicates and lossy casts, and version-check the device's library.

for a principal

Decide how a team's migration tooling handles SQLite: always rebuild, or branch on version for 3.53.0 forms, and what testing a released migration needs.

## Why SQLite's ALTER TABLE is narrow Most engines keep their schema in parsed system tables, so ALTER TABLE edits rows in a catalogue. SQLite stores each schema object in the `sqlite_schema` table as the **original text** of its `CREATE` statement. Every ALTER TABLE therefore edits that text and then re-parses the whole schema, and it succeeds only if the schema is still valid afterwards. Storing text keeps each database file small and lets the in-memory representation change between releases, but it means SQLite supports only the alterations it can make safely to the text. The cost of each form differs. A rename, or adding a column with no constraints, changes only the schema text and runs as fast on ten million rows as on one. Dropping a column rewrites every row, and adding a CHECK or NOT NULL constraint reads every row to test it. ## What ALTER TABLE can do directly, as of 3.53 | Form | Since | What to know | |---|---|---| | `RENAME TO` | long-standing | References in triggers and views follow since 3.25.0, foreign keys whatever `PRAGMA foreign_keys` says since 3.26.0; cannot move a table between attached databases | | `RENAME COLUMN` | 3.25.0 | Also renames the column in indexes, triggers and views; fails if a trigger or view would become ambiguous | | `ADD COLUMN` | long-standing | Always appended as the last column | | `DROP COLUMN` | 3.35.0 | Rewrites the table's content to purge the column | | `ALTER COLUMN c SET NOT NULL` / `DROP NOT NULL` | 3.53.0 | SET scans the rows and fails if any value is NULL | | `ADD [CONSTRAINT name] CHECK (expr)` / `DROP CONSTRAINT name` | 3.53.0 | ADD CHECK tests every existing row; DROP CONSTRAINT removes only a named CHECK or NOT NULL constraint | `ADD COLUMN` refuses a column that: - has a PRIMARY KEY or UNIQUE constraint; - defaults to `CURRENT_TIME`, `CURRENT_DATE`, `CURRENT_TIMESTAMP` or an expression in parentheses; - is NOT NULL without a non-NULL default; - has a REFERENCES clause with a default other than NULL, while foreign keys are enforced; - is a `GENERATED ALWAYS ... STORED` column (VIRTUAL ones are allowed). `DROP COLUMN` fails when the column is part of the PRIMARY KEY, has a UNIQUE constraint, is indexed, appears in a partial index's WHERE clause, in another CHECK constraint, in a foreign key or a generated column's expression, or in a trigger or view. Changing a column's type, adding UNIQUE or a primary key, adding a foreign key and reordering columns are not on the list. ## The rebuild for everything else The documented procedure works for any change, including ones that alter the stored data: 1. If foreign keys are enforced, run `PRAGMA foreign_keys=OFF`. It must come before `BEGIN`, because inside a transaction this pragma is a no-op. 2. `BEGIN` a transaction. 3. Save the SQL of the table's indexes, triggers and views: `SELECT type, sql FROM sqlite_schema WHERE tbl_name='X'`. 4. `CREATE TABLE new_X (...)` in the new shape. 5. Copy the data: `INSERT INTO new_X (...) SELECT ... FROM X`. 6. `DROP TABLE X`. 7. `ALTER TABLE new_X RENAME TO X`. 8. Recreate the indexes, triggers and views from step 3, adjusted for the change; drop and recreate any view that the change affects. 9. If foreign keys were on, run `PRAGMA foreign_key_check` to find broken references. 10. `COMMIT`, then turn foreign keys back on. The order matters. Renaming the old table out of the way first ("rename X to old_X, create X, copy, drop old_X") looks equivalent but is not: since 3.25.0 and 3.26.0, renaming a table also rewrites the triggers, views and foreign keys that point at it, so they would follow old_X and break when it is dropped. Building `new_X` and renaming it into place leaves those references alone. For changes that leave the stored data untouched, such as removing a constraint or changing a default, the documentation also describes editing `sqlite_schema` directly with `PRAGMA writable_schema=ON` and bumping `PRAGMA schema_version`. A typo there leaves the database corrupt and unreadable, so treat it as a last resort, rehearsed on a copy. ## Production judgment - **Check the version you actually run.** A phone's built-in SQLite lags the current release and may predate 3.53.0, or even 3.35.0. On an older library the newer ALTER forms are syntax errors, so either branch on `sqlite_version()` or use the rebuild, which works everywhere. - **Check the data before the copy.** Adding UNIQUE fails on the first duplicate, so look first with `SELECT sku, count(*) FROM products GROUP BY sku HAVING count(*) > 1`. Changing a type needs a plan for values that will not convert, because `CAST` is lossy: `CAST('$3.50' AS REAL)` is 0.0. - **Keep it in one transaction**, so a failed step leaves the old table intact. A rebuild rewrites the whole table and holds the write lock for its duration; how that interacts with other connections is a separate subject.

  • In SQLite, why does ADD COLUMN with a default of CURRENT_TIMESTAMP fail when the same column is legal in CREATE TABLE?
    SQLite documents the restriction, and it follows from how ADD COLUMN works: it edits only the schema text and leaves existing rows untouched, so those rows report the declared default for the new column, which therefore has to be a constant. Add the column with a constant or NULL default and backfill it with an UPDATE, or use the rebuild.
  • In SQLite 3.53, can ALTER TABLE ... DROP CONSTRAINT remove an unnamed UNIQUE constraint?
    No. DROP CONSTRAINT finds a constraint by its name and removes only CHECK or NOT NULL constraints; naming any other kind fails with `constraint may not be dropped`. An unnamed constraint cannot be addressed at all. Removing a UNIQUE constraint still takes the rebuild procedure.
  • A SQLite rebuild ran with foreign keys enforced the whole time. What can go wrong?
    With enforcement on, `DROP TABLE X` performs an implicit delete of its rows, which can fire ON DELETE CASCADE actions or fail with a foreign key error in child tables. That is why the procedure turns `foreign_keys` off before `BEGIN` and runs `PRAGMA foreign_key_check` before committing.

  • SQLite's ALTER TABLE can change a column's type with ALTER COLUMN TYPE
  • PRAGMA foreign_keys=OFF works inside an open transaction
  • Renaming the old table first and then creating the new one is equally safe
  • ADD COLUMN can add a UNIQUE column if the table is empty
  • Every SQLite on a phone supports the 3.53.0 ALTER forms

Assumes SQLite 3.53; ALTER COLUMN SET/DROP NOT NULL, ADD CHECK and DROP CONSTRAINT arrived in 3.53.0 (2026-04-09), DROP COLUMN in 3.35.0 and RENAME COLUMN in 3.25.0.