Recommended Free Tools
SQLite can rename tables and columns, add columns, and drop eligible columns without replacing the table. Since SQLite 3.53.0, it can also set or drop a column’s NOT NULL constraint. Most other structural changes—including changing a column’s type or position, or changing key and constraint definitions—require the documented replacement-table procedure. The right choice depends on the SQLite version your application actually runs and on the table’s dependencies.
Which schema changes can SQLite handle directly?
SQLite’s ALTER TABLE documentation describes a limited set of direct schema changes. Use this table to choose a starting point; a supported operation can still fail if the particular table definition or its dependencies violate the operation’s rules.
| Desired change | Direct operation? | When a rebuild or further investigation is needed |
|---|---|---|
| Rename a table | Yes: ALTER TABLE ... RENAME TO ... |
Usually no rebuild. Check behavior on older SQLite versions and the effects on dependent schema objects. |
| Rename a column | Yes: ALTER TABLE ... RENAME COLUMN ... TO ... |
Usually no rebuild. The operation fails if the rename makes a trigger or view ambiguous. |
| Add a column | Yes: ALTER TABLE ... ADD COLUMN ... |
Rebuild or redesign the migration if the desired definition violates ADD COLUMN restrictions. |
| Drop a column | Yes, if the column is eligible | Rebuild if the column is a primary key or unique, or remains referenced by an index, constraint, foreign key, generated-column expression, trigger, or view. |
Set or drop NOT NULL |
Yes, starting with SQLite 3.53.0 | On older runtime versions, use the replacement-table procedure if the change is required. |
| Change a column’s type or position; add, remove, or change a primary key, unique, CHECK, or foreign-key structure | No general direct ALTER operation | Use the replacement-table procedure. |
SQLite 3.53.0, released April 9, 2026, added ALTER COLUMN ... SET NOT NULL and ALTER COLUMN ... DROP NOT NULL. Check the library version in the running application rather than assuming it matches a developer’s workstation; apps may bundle a different SQLite build. See SQLite’s version notes and syntax.
What can block a direct ALTER operation?
Adding a column
ADD COLUMN appends the field to the end of the table. It cannot add a PRIMARY KEY or UNIQUE constraint. A default cannot be CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or a parenthesized expression. A new NOT NULL column must have a non-NULL default. If foreign-key enforcement is enabled, a new column with a REFERENCES clause must have a NULL default. A STORED generated column cannot be added this way, though a VIRTUAL generated column can.
#1 Best Overall
Adding a CHECK constraint, or adding NOT NULL to a generated column, causes SQLite to test existing rows. These checks have been performed since SQLite 3.37.0, released November 27, 2021. This is different from an unconstrained add that can avoid rewriting table contents.
Dropping a column
DROP COLUMN removes the column’s stored content, so it rewrites table content rather than merely changing schema metadata. SQLite rejects the operation if the column is a primary key or unique, or if it is still used by an index or partial-index predicate, another CHECK constraint, a foreign key, a generated-column expression, a trigger, or a view. Remove or update those dependencies as part of a deliberate migration; otherwise, use a rebuild that defines the intended result.
Rank #2
SQLite added DROP COLUMN in version 3.35.0, released March 12, 2021. Applications running older versions need another migration approach.
Renaming a table or column
Renames generally avoid copying table data. Since SQLite 3.25.0, table renames update references in triggers and views; since 3.26.0, they also update foreign-key references regardless of the foreign_keys setting, subject to the documented legacy compatibility setting. Column renames update references in indexes, triggers, and views. A column rename fails atomically if it would make a trigger or view semantically ambiguous. Older library versions can behave differently, so check the version and compatibility settings when a migration must support older databases.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Rank #3
How to rebuild a table safely
When no direct operation fits, SQLite’s documented general method is to create a replacement table, copy the data, and restore dependent objects. Treat this as a data migration as well as a schema change: decide how old values map to the new columns and how any newly required values will be supplied.
- Record the initial foreign-key setting. If foreign-key constraints are enabled, disable them before beginning the transaction. Changing this setting inside the transaction is not the documented procedure.
- Start a transaction. Keep the schema change and data copy together so they can be committed as one migration.
- Save dependent definitions and inspect dependencies. Record the SQL for the table’s indexes and triggers, and identify affected views, including views that refer to the table.
- Create a new table under an unused temporary name. Give it the intended schema. Create the replacement first; do not first rename the old table and then create a new table under the original name.
- Copy and transform the data. Use an explicit mapping when columns differ. For example, name destination columns in the INSERT and select the corresponding source columns or expressions from the old table. SQLite’s basic pattern is
INSERT INTO new_X SELECT ... FROM X. - Drop the old table. Do this only after the replacement has been populated successfully within the transaction.
- Rename the replacement table to the original name. Use the table-rename operation after dropping the old table.
- Recreate indexes and triggers, and update affected views. Restore suitable definitions for all objects recorded during dependency inspection.
- Check foreign keys if they were originally enabled. Run
PRAGMA foreign_key_checkand resolve any reported violations. - Commit, then restore foreign-key enforcement if it was enabled before the migration.
SQLite warns against renaming the old table to a temporary name as the first step. Enhanced rename behavior can rewrite references in triggers, views, and foreign-key constraints in ways that break that pattern. Follow the new-table-first sequence in the official procedure.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How much work does each approach do?
SQLite stores schema definitions as SQL text in sqlite_schema. A table or column rename, and an ADD COLUMN that does not require checking existing rows, can be independent of the number of rows because they do not rewrite table content. Adding certain constraints requires SQLite to read existing rows for validation. DROP COLUMN rewrites table content to remove the field. A rebuild copies rows into a new table and recreates dependent objects, so its work depends on table size and any data transformation.
- Syntax: Is there a direct ALTER operation for the intended change?
- Eligibility: Does this table’s schema satisfy that operation’s restrictions?
- Data work: Will SQLite scan rows or rewrite table content?
- Dependencies: Which indexes, triggers, views, and foreign keys need updating, restoration, or validation?
Why not edit SQLite’s schema directly?
PRAGMA writable_schema=ON can disable schema parse checking for some ALTER operations, but it is an advanced technique, not a routine alternative to rebuilding. Direct edits to sqlite_schema can leave a database corrupt and unreadable if the SQL text is wrong. Prefer the supported ALTER syntax or the replacement-table procedure unless you have a narrowly defined reason, a reliable backup, and a tested recovery plan. SQLite’s documentation describes the warning and the relevant behavior.
Quick Recap
Best Value
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.
