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?
answer
- the schema is stored as text
- rename, add, drop, and little else
- 3.53.0 added constraint edits
- build new, copy, drop, rename
- foreign keys off before BEGIN
basics
~20 sSQLite'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 sSQLite 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 linesPRAGMA 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
Recall that SQLite's ALTER TABLE renames tables and columns and adds or drops columns, and that most other changes need a new table.
List the ADD COLUMN and DROP COLUMN restrictions, the 3.53.0 constraint forms, and why the schema-as-text design limits ALTER TABLE.
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.
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.