SQLite has no general ALTER TABLE ... ALTER COLUMN ... TYPE command. To change a column’s declared type, rebuild the table: create its replacement, copy across the rows and values you want, remove the old table, rename the replacement, and restore affected indexes, triggers, and views. The replacement definition and copy query determine what survives.
Does SQLite support changing a column type with ALTER COLUMN?
No. SQLite’s documented schema-change procedure handles a general column-type change by rebuilding the table, rather than altering a column’s declared type in place. 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 the general datatype. Check the SQLite version used by your application before relying on version-specific syntax. SQLite ALTER TABLE documentation
As an Amazon Associate I earn from qualifying purchases.
What does a SQLite table rebuild delete?
Dropping the original table removes that table and its stored rows. The copy query controls which data moves to the replacement: any column or row omitted from the query is not carried over. Indexes and triggers associated with the old table must be recreated as applicable, and views affected by the schema change may need to be dropped and recreated. SQLite ALTER TABLE documentation
A declared type change does not, by itself, convert every existing value. SQLite uses dynamic typing: a column’s declared type determines its affinity, but does not strictly restrict all values to that storage class. If you intend to convert values, make the conversion explicit in the copy expression—for example, with CAST—and decide how to handle values for which conversion would be invalid or lossy. SQLite CREATE TABLE documentation
#1 Best Overall
How do you change a column type safely?
Follow SQLite’s create-copy-drop-rename order. In particular, do not rename the old table first: that can change references in triggers, views, and foreign-key constraints. The official guide warns: “Take care to follow the procedure above precisely.” SQLite ALTER TABLE documentation, “Making Other Kinds Of Table Schema Changes”
-
If foreign keys are enabled, turn them off before starting the transaction. Keep track of whether they were enabled so you can restore enforcement afterward.
-
Begin a transaction.
-
Record the SQL for relevant indexes, triggers, and views before changing the schema. One way to inspect objects associated with table
XisSELECT type, sql FROM sqlite_schema WHERE tbl_name='X';Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesSpecial offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.Rank #2
-
Create a replacement table, such as
new_X, with the intended column declaration and all required constraints. -
Copy the data you intend to retain, using an explicit column list and any deliberate conversion expressions. For example:
INSERT INTO new_X (id, amount) SELECT id, CAST(amount AS REAL) FROM X;Choose expressions that match your data and validate the result; the example is not a guarantee that every value can be converted without loss. -
Drop the original table,
X. -
Rename
new_XtoX. -
Recreate applicable indexes and triggers, and update and recreate views affected by the change.
Rank #3
-
If foreign keys were enabled before the migration, run
PRAGMA foreign_key_check;before committing.Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Commit the transaction, then re-enable foreign keys if they were enabled beforehand.
This is migration guidance, not a guarantee for every schema. Review application dependencies and validate the copied data and resulting schema before treating the migration as complete.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Which constraints and dependencies must be rebuilt?
The replacement table’s CREATE TABLE statement defines its constraints; a data copy does not automatically carry them forward. Review the old definition and deliberately specify the primary key, uniqueness rules, NOT NULL, CHECK, and foreign-key constraints required in the new table. SQLite CREATE TABLE documentation
-
Indexes: Save and recreate the applicable index definitions after the replacement has the original table name.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchSpecial offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Triggers: Save and recreate applicable triggers; inspect their references for assumptions affected by the schema change.
Best Value
-
Views: Identify views that depend on the changed table or columns, then update and recreate affected views.
-
Foreign keys: If enabled, follow SQLite’s prescribed handling and run the foreign-key check before commit. With foreign keys enabled, dropping a table performs an implicit delete, which can invoke foreign-key actions or fail because of a constraint violation. SQLite Foreign Key Support
Quick Recap
Bestseller No. 1
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.
