Why SQLite refuses your ALTER TABLE, and the twelve-step rebuild it wants instead
Coming from Postgres, SQLite's ALTER TABLE feels broken. It is not. It is small on purpose, and the rules for what it refuses are consistent once you know them. The trouble is that the refusals happen at run time, and a migration that fails at run time fails in production too. What ALTER TABLE can do Rename a table. Rename a column. Add a column, with restrictions. Drop a column, since 3.35, with…
SQLite's ALTER TABLE functionality is limited compared to other database systems. It allows renaming tables and columns, adding new columns with certain restrictions, and dropping columns but with additional limitations. There are no ALTER COLUMN, changing types, adding or dropping constraints, or adding primary keys to existing tables.
The add-column restrictions require a constant default value and cannot include NOT NULL, UNIQUE, or a timestamp default. Dropping a column with an index, constraint, or view is also not permitted. The recommended solution for complex migrations is a twelve-step rebuild process involving creating a new table with the desired schema, copying data, dropping the old table, and renaming the new one.
This process ensures data integrity and avoids potential issues with foreign keys, indexes, triggers, and views. The SQLite documentation provides a twelve-step rebuild procedure for migrations that ALTER TABLE cannot accomplish.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
