Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
SQLite has no general ALTER TABLE ... ALTER COLUMN ... TYPE command. To change a column’s declared type, rebuild the table: create a replacement with the intended definition, copy the rows and values you want to keep, drop the old table, rename the replacement, then restore affected indexes, triggers, and views. The replacement definition and copy query determine what survives—and whether values are actually converted.
Does SQLite support ALTER COLUMN for a type change?
No. SQLite’s ALTER TABLE supports a limited set of schema changes, but changing a column’s declared datatype requires the documented table-rebuild procedure. SQLite 3.53.0, released April 9, 2026, added ALTER COLUMN ... SET NOT NULL and ALTER COLUMN ... DROP NOT NULL. Those commands change nullability, not a column’s datatype. Check the SQLite version used by your application before relying on version-specific syntax. See the SQLite ALTER TABLE documentation.
What does changing a column type actually change?
In SQLite, a declared type determines a column’s type affinity; it does not strictly force every stored value to have that storage class. Changing the declaration alone therefore does not guarantee that existing values are converted. If you want conversion, put the intended expression—such as a deliberate CAST—in the data-copy query, and decide how to handle values that cannot be converted cleanly or would lose information. SQLite explains affinity and table definitions in its CREATE TABLE documentation.
What does a SQLite table rebuild delete?
Dropping the original table removes that table and its stored rows. The copy query controls which rows and values reach the replacement: anything omitted from the selected columns or filtered out of the query is not preserved by the copy. Indexes and triggers associated with the old table must be saved and recreated as applicable; affected views may also need to be updated and recreated.
#1 Best Overall
The new CREATE TABLE statement must specify the constraints you intend to retain, including primary keys, uniqueness, NOT NULL, CHECK, and foreign-key definitions. A data copy does not automatically restore those parts of the schema.
How to change a column type safely
Use SQLite’s create-copy-drop-rename order. Its documentation warns that renaming the old table first can change references in triggers, views, and foreign-key constraints. The rebuild can also involve work proportional to the table’s contents, so plan for the size of the table and validate the result before treating the migration as complete.
Rank #2
- Check foreign-key enforcement. If foreign keys are enabled, turn them off before starting the transaction, as SQLite’s documented procedure directs. Remember whether they were enabled so you can restore that setting afterward.
- Begin a transaction. This groups the schema and data changes so they can be committed together after validation.
- Record dependent schema objects. Save the SQL for relevant indexes, triggers, and views before dropping the table. SQLite suggests inspecting associated objects with
SELECT type, sql FROM sqlite_schema WHERE tbl_name='X';, replacingXwith the table name. - Create the replacement table. Define
new_Xwith the desired column declaration and all intended constraints. Review the full table definition, not just the changed column. - Copy the intended data. Use an explicit destination column list and a matching
SELECTlist. Apply any deliberate conversion in the expressions, and account for rows or values that should not be copied. - Drop the old table. After the copy, drop
X. Do not rename it before creating and preparing the replacement. - Rename the replacement. Rename
new_XtoX, the original table name. - Restore dependent objects. Recreate applicable indexes and triggers, and update and recreate views affected by the schema change.
- Check foreign keys if they were previously enabled. Run
PRAGMA foreign_key_check;before committing and address any reported violations. - Commit and restore enforcement. Commit only after checking the rebuilt schema and data. Re-enable foreign keys if they were enabled before the migration.
SQLite’s ALTER TABLE guide cautions: “Take care to follow the procedure above precisely.” That warning accompanies its explanation of why the rename-first alternative can corrupt references. See Making Other Kinds Of Table Schema Changes.
Why foreign keys need special attention
When foreign keys are enabled, dropping a table performs an implicit delete, which can invoke foreign-key actions or fail because of a constraint violation. Disabling enforcement is not a substitute for validating the rebuilt database: follow the documented sequence, run the foreign-key check when applicable, and restore enforcement. See SQLite’s Foreign Key Support documentation.
Quick Recap
Best Value
Rank #3
Before committing, verify what the migration preserved
- Confirm the replacement has the intended declared type, constraints, and column order.
- Check row counts and inspect representative converted values, including values that may be invalid or lossy under the chosen conversion.
- Confirm expected indexes and triggers exist, and that affected views work with the revised schema.
- If foreign keys were enabled, ensure
PRAGMA foreign_key_check;reports no violations.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
