ALTER TABLE users ALTER COLUMN age TYPE INTEGER;
-- Error: near "ALTER": syntax error
SQLite's ALTER TABLE is deliberately minimal. Coming from PostgreSQL or MySQL, it's surprising how many schema changes it can't do directly. You can still make any change you need; it just takes the "rebuild the table" procedure, done safely.
What ALTER TABLE supports
| Change | Supported since |
|---|---|
ALTER TABLE t RENAME TO t2 | always |
ALTER TABLE t ADD COLUMN c … | always (with restrictions) |
ALTER TABLE t RENAME COLUMN a TO b | 3.25.0 (2018) |
ALTER TABLE t DROP COLUMN c | 3.35.0 (2021) |
Check your version with SELECT sqlite_version();. Language bindings often bundle an older SQLite than your command-line tool.
ADD COLUMN restrictions: the new column can't be PRIMARY KEY or UNIQUE, can't have a non-constant default such as CURRENT_TIMESTAMP, and if it's NOT NULL it needs a non-null default.
DROP COLUMN fails if the column is part of the primary key, has a UNIQUE constraint, is indexed, is used in a foreign key, a CHECK, a generated column, a partial index, a trigger or a view. Drop those dependencies first.
Everything else needs a rebuild: changing a column's type, adding or removing constraints (NOT NULL, CHECK, UNIQUE, foreign keys), changing a default, or reordering columns.
The safe rebuild procedure
This is the procedure recommended in SQLite's documentation, simplified:
PRAGMA foreign_keys = OFF;
;
users_new (
id ,
email TEXT ,
age (age ),
created_at TEXT (datetime())
);
users_new (id, email, age, created_at)
id, email, (age ), (created_at, datetime())
users;
users;
users_new RENAME users;
INDEX users_created_at_idx users (created_at);
PRAGMA foreign_key_check;
;
PRAGMA foreign_keys ;
