SQLite drop column with Schema Diff Studio
A users table is loaded before and after a clean-up that SQLite's ALTER TABLE cannot do: an indexed column is dropped, a TEXT column becomes INTEGER, an existing column gets a foreign key and a new UNIQUE rule. The comparison runs on load and opens the Migration tab with the rebuild: new table, row copy, drop, rename, indexes recreated, foreign keys checked. It is verified in SQLite in your tab.
- Paste sqlite3 app.db .schema output into Before, or open the .db file (only the schema is read). Write the table as it should end up into After.
- Press Compare. The Changes tab shows "Rebuild table users" with every reason: drop column legacy_token, age type TEXT → INTEGER, foreign key on team_id, unique on username.
- Open Migration and read the order: PRAGMA foreign_keys = OFF, BEGIN, CREATE TABLE users__new, INSERT ... SELECT the shared columns, DROP TABLE users, rename, recreate the indexes, PRAGMA foreign_key_check, COMMIT, foreign keys back on.
- Back up the database file, run the script on a copy first, and check that PRAGMA foreign_key_check returns no rows before you ship it.
What to know
SQLite 3.35 (March 2021) added ALTER TABLE DROP COLUMN, but it refuses many columns: a PRIMARY KEY, a UNIQUE column, an indexed column, one used in a partial index WHERE, in a CHECK that also names another column, in a foreign key, in a generated column, or in a trigger or view. legacy_token here is indexed, so DROP COLUMN fails with "error in index users_legacy_token after drop column". Dropping the index first would work for this one case; the rebuild works for every case and on older SQLite builds still shipped in some apps.
Changing a type, adding a foreign key to an existing column, adding UNIQUE or NOT NULL to one, or changing a default all have no ALTER form in SQLite. The documented way is the 12-step rebuild: create the table under a temporary name with the new definition, copy the rows across, drop the old table, rename the new one, and recreate the indexes, triggers and views that pointed at it. The script turns foreign key enforcement off first, because dropping a parent table with enforcement on would act on the child rows, and that PRAGMA only works outside a transaction.
SQLite does not convert stored values when a column's declared type changes. The copy inserts each age value into an INTEGER-affinity column, where text that looks like a whole number ("42") is stored as an integer but anything else ("unknown", "") stays text. Unless the table is STRICT (SQLite 3.37+), nothing rejects those values, so query for them before or after: SELECT id, age FROM users WHERE typeof(age) <> 'integer'.
The copy keeps the row ids because id is copied as a column, so foreign keys from other tables still match. PRAGMA foreign_key_check after the copy lists any row whose new team_id points to a team that does not exist; the transaction should not be committed until it is empty. A UNIQUE rule that existing duplicates violate fails at the INSERT ... SELECT, so the whole rebuild rolls back and the original table is untouched.
Updated · Cosmovex
Questions
Does SQLite support ALTER TABLE DROP COLUMN?
Since version 3.35.0, yes, but not for columns that are indexed, part of the primary key, UNIQUE, used in a foreign key, a CHECK on other columns, a generated column, a trigger or a view. For those, and for older SQLite versions, rebuild the table as this tool generates.
How do I change a column type in SQLite?
There is no ALTER COLUMN in SQLite. Create a new table with the new type, copy the rows with INSERT INTO new SELECT ... FROM old, drop the old table and rename the new one, then recreate its indexes and triggers. Existing values are copied as they are; SQLite only converts them when the new type affinity allows it.
How do I add a foreign key to an existing column in SQLite?
Only by rebuilding the table with the REFERENCES clause in its new CREATE TABLE. A brand-new column can be added with a foreign key via ADD COLUMN, as long as its default is NULL.
Why must PRAGMA foreign_keys be turned off outside the transaction?
SQLite ignores PRAGMA foreign_keys inside a transaction. With enforcement on, DROP TABLE on the old table would apply ON DELETE actions or fail on rows in child tables. Turning it off before BEGIN and checking with PRAGMA foreign_key_check before COMMIT keeps the data consistent.
Do indexes and triggers survive the rebuild?
They are dropped with the old table, so the script recreates every index from your After schema, and triggers and views that depend on the table are recreated as well. Verification compares the rebuilt schema with After, so a missing index would show as a difference.
The free plan covers everything on this page. Schema Diff Studio Pro ($29, paid once) is described on the Schema Diff Studio page.
