Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 33 ยท Schema Design and Integrity

Altering Schema Safely

Change SQLite tables that already hold data: what ALTER TABLE can and cannot do, the safe table-rebuild recipe, sqlite_master, and schema versions with user_version.

Schemas are never finished. A column needs renaming, a type was a mistake, a constraint is missing. In a client-server database you would reach for one ALTER TABLE and be done. SQLite's ALTER TABLE is deliberately small: it can add, rename and (since 3.35) drop columns, and rename tables, but it cannot change a column's type or add most constraints to an existing column.

For everything else the answer is a recipe: build a new table, copy the data, drop the old one, rename the new one. This lesson covers the easy cases, the limits, the rebuild recipe with its safety rules, how to look at the schema through sqlite_master, and how to track schema versions with PRAGMA user_version so that migrations run exactly once.

What ALTER TABLE can do

ADD COLUMN Cheap, even on huge tables.
RENAME Tables and columns.
DROP COLUMN Since 3.35, with restrictions.

Anything else needs a table rebuild.

1 / 2

The easy changes

ADD COLUMN appends a column. SQLite only edits the stored table definition, so it is nearly instant no matter how many rows exist; old rows simply read the column's default. RENAME COLUMN and RENAME TO update the table definition and every reference to it in indexes, triggers and views. DROP COLUMN removes a column's data from the table.

SQL Add, rename and drop a column

Press Run (or Ctrl + Enter) to see the result here.

Existing rows pick up the default n/a for the new column. After the rename and drop the table has the columns id, title, qty and note, and a final rename turns the table into devices with both rows intact.

What ADD COLUMN refuses

ADD COLUMN has rules, because SQLite does not rewrite existing rows:

  • A NOT NULL column needs a non-NULL default, otherwise the statement fails when the table has rows.
  • The default must be a constant. Expressions like datetime('now') are rejected ("non-constant default"). CURRENT_TIMESTAMP is rejected too.
  • The new column cannot be PRIMARY KEY or UNIQUE.
  • A generated column can be added only if it is VIRTUAL (see Generated columns and JSON).
  • A column with a foreign key must default to NULL when foreign keys are enforced.

DROP COLUMN also has limits: you cannot drop a PRIMARY KEY or UNIQUE column, an indexed column, a column used in a CHECK, foreign key, view, trigger or generated column, or the only column of a table. Drop the index or view first, or use the rebuild below.

SQL A refused ALTER

Press Run (or Ctrl + Enter) to see the result here.

The first ADD COLUMN works. The second fails with Cannot add a column with non-constant default. The workaround is to add the column with a constant default or NULL, then fill it with an UPDATE, and put the dynamic default in the INSERT code or a trigger.

The table rebuild

To change a column's type, add a CHECK or NOT NULL constraint, change a default, reorder columns or fix a foreign key, rebuild the table. The SQLite documentation describes a twelve-step procedure; the essential steps are these:

  1. Turn off foreign keys (PRAGMA foreign_keys = OFF), because dropping a table can otherwise cascade or fail. This pragma must be set outside a transaction.
  2. BEGIN a transaction so a mistake rolls back cleanly.
  3. Remember the SQL of indexes, triggers and views that belong to the table (from sqlite_master).
  4. CREATE TABLE new_name with the new definition.
  5. INSERT INTO new_name SELECT ... FROM old_name, converting values as needed.
  6. DROP TABLE old_name.
  7. ALTER TABLE new_name RENAME TO old_name.
  8. Recreate the indexes, triggers and views from step 3.
  9. Run PRAGMA foreign_key_check to prove nothing is broken.
  10. COMMIT, then turn foreign keys back on.
SQL Rebuilding a table to change a column type

Press Run (or Ctrl + Enter) to see the result here.

Before the rebuild the age is stored as text; afterwards both rows hold integers, and the new CHECK rejects negative ages. The schema listing shows the index people_name, the table people and the view people_names all recreated. Dropping the table removed its index automatically, which is why we create it again.

The order matters in two places. We drop the view before the table swap, because renaming a table that a view depends on can fail with error in view ...: no such table, and we create the new table under a temporary name and rename it (rather than renaming the old table away first), which keeps references from other tables pointing at the right name. The copy in step 5 is also your chance to clean data, so write the SELECT carefully and compare row counts before dropping anything.

On a real database file, take a backup before a rebuild (see Backup, VACUUM and maintenance). Run the whole thing inside a transaction, and test the migration on a copy of production data first.

Looking at the schema: sqlite_master

Every SQLite database has a hidden table named sqlite_master (also sqlite_schema) that stores the CREATE statement of every table, index, view and trigger. Querying it is how tools such as the .schema shell command work.

SQL Reading sqlite_master

Press Run (or Ctrl + Enter) to see the result here.

The first query lists two tables, one index and one view. (A UNIQUE constraint would add an automatic entry named sqlite_autoindex_...; we declared none.) The second returns the exact CREATE TABLE text you wrote. The last uses a table-valued pragma function, pragma_table_info, to get one row per column with its type, NOT NULL flag and primary key position. Similar functions exist for pragma_index_list and pragma_foreign_key_list.

You can read sqlite_master, but do not write to it by hand, except through documented advanced procedures. A mistake can corrupt the database.

Schema versions with user_version

How does an application know which migrations a given database file has already received? SQLite reserves a 32-bit integer in the file header for you: PRAGMA user_version. It starts at 0, SQLite itself never touches it, and you can read and set it freely.

A migration runner works like this: read user_version, run every migration numbered higher, and set the new version, all in one transaction so that a failure leaves the old version in place.

SQL Reading and setting user_version

Press Run (or Ctrl + Enter) to see the result here.

The database starts at version 0, and after the two migrations it reports 2 and the table has the columns key, value and updated. In application code the pattern looks like this (read-only; it needs a file database and a driver):

Python
import sqlite3

MIGRATIONS = [
    "CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT)",
    "ALTER TABLE settings ADD COLUMN updated INTEGER DEFAULT 0",
]

con = sqlite3.connect("app.db", isolation_level=None)  # we send BEGIN/COMMIT ourselves
version = con.execute("PRAGMA user_version").fetchone()[0]
for number, sql in enumerate(MIGRATIONS[version:], start=version + 1):
    con.execute("BEGIN")
    try:
        con.execute(sql)
        con.execute(f"PRAGMA user_version = {number}")
        con.execute("COMMIT")
    except Exception:
        con.execute("ROLLBACK")
        raise

Only the migration list grows over time; old databases catch up automatically on start. Never edit a migration after it has shipped: add a new one.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which change can ALTER TABLE do directly in SQLite?

    SQLite can add, rename and drop columns and rename tables. Type changes, new constraints and reordering need a table rebuild.
  2. 2. Why does ALTER TABLE t ADD COLUMN created TEXT DEFAULT (datetime('now')) fail?

    ADD COLUMN does not rewrite existing rows, so the default must be a constant value that every old row can share.
  3. 3. Where must PRAGMA foreign_keys = OFF be run for a table rebuild?

    The pragma has no effect inside a transaction, so set it before BEGIN and switch it back on after COMMIT.
  4. 4. What is PRAGMA user_version for?

    user_version is a free integer slot in the database file header. SQLite never changes it, so migration code can use it as the schema version.

Summary

  • ADD COLUMN, RENAME COLUMN, RENAME TO and DROP COLUMN cover the easy cases; added columns need constant defaults and cannot be PRIMARY KEY or UNIQUE.
  • For type changes, new constraints or reordering, rebuild: create the new table, copy with INSERT ... SELECT, drop the old one, rename the new one, then recreate indexes, triggers and views.
  • Set PRAGMA foreign_keys = OFF outside the transaction, do the rebuild inside one, and finish with PRAGMA foreign_key_check.
  • sqlite_master and pragma_table_info() let you inspect the schema in SQL.
  • PRAGMA user_version is the schema version slot for migration runners; update it in the same transaction as the migration.

Next: Generated columns and JSON, computed values and semi-structured data.