ALTER TABLE Capabilities and SQLite’s Schema-Rewrite Model

Learning outcomes

A schema migration is a program that changes the structure and meaning of persistent data. SQLite makes many common changes easy, but it does not pretend that every table redesign can be expressed as one ALTER TABLE clause. The reliable mental model is: understand the currently supported direct operations, understand SQLite’s schema-as-text design, and use a deliberate rebuild when the desired change exceeds those operations.

What ALTER TABLE means in current SQLite

SQLite’s direct schema-change surface has expanded over time. Current SQLite 3.53.4 supports table rename, column rename, column addition, column drop, and—new in 3.53.0—setting or dropping a column’s NOT NULL constraint. Older tutorials that say SQLite can only rename tables or add columns are historical, not current.

Why SQLite schema changes feel different

SQLite stores the original CREATE TABLE, CREATE INDEX, CREATE VIEW, and CREATE TRIGGER SQL in sqlite_schema. A direct ALTER operation rewrites that SQL text and reparses the schema. This design keeps the file format compact and portable, but it means a schema change must leave every dependent definition valid.

SELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE tbl_name IN ('device', 'maintenance_note')   OR name LIKE 'idx_%'ORDER BY type, name;PRAGMA table_xinfo('maintenance_note');PRAGMA index_list('maintenance_note');PRAGMA foreign_key_list('maintenance_note');

This inspection is not ceremony. It tells you which objects the migration must preserve or intentionally replace. A table definition is only one part of the database contract.

Simple alteration: add a controlled column

Suppose FieldNotes wants to record where each maintenance note originated. Existing rows need a valid value, so this is a good direct ADD COLUMN case.

DROP TABLE IF EXISTS note_alter_demo;CREATE TABLE note_alter_demo(    note_id     INTEGER PRIMARY KEY,    note_text   TEXT NOT NULL);INSERT INTO note_alter_demo(note_text)VALUES ('Inspect coupling'), ('Check bearing temperature');ALTER TABLE note_alter_demoADD COLUMN source_system TEXT NOT NULL DEFAULT 'manual'    CHECK (source_system IN ('manual','sensor','import'));SELECT note_id, note_text, source_systemFROM note_alter_demoORDER BY note_id;PRAGMA table_xinfo('note_alter_demo');

Expected state: the two preexisting rows read as source_system='manual'. SQLite also validates an added CHECK constraint against existing rows in current releases. By contrast, ADD COLUMN x TEXT UNIQUE is not allowed, and a non-NULL added column requires a non-NULL default when old rows already exist.

DROP COLUMN is not “delete this name from CREATE TABLE”

Dropping a column can fail for reasons that are healthy: SQLite refuses to leave schema objects pointing at something that no longer exists. Treat those failures as dependency information, not as an invitation to disable checking.

DROP TABLE IF EXISTS drop_demo;CREATE TABLE drop_demo(    id INTEGER PRIMARY KEY,    code TEXT NOT NULL,    note TEXT);CREATE INDEX idx_drop_demo_code ON drop_demo(code);-- Current SQLite rejects this while the index depends on code.ALTER TABLE drop_demo DROP COLUMN code;-- A real migration would decide whether the index itself should disappear,-- then remove/recreate dependent objects deliberately.

Rename compatibility and legacy_alter_table

Modern rename behavior was improved in SQLite 3.25.0/3.26.0 so references inside triggers, views, and foreign keys are normally updated. PRAGMA legacy_alter_table=ON restores older rename behavior for applications that depended on it. That makes it a compatibility escape hatch—not a recommended default.

SELECT sqlite_version();PRAGMA legacy_alter_table;PRAGMA foreign_keys;-- Modern application policy:-- leave legacy_alter_table OFF unless supporting a known old behavior contract.

A change ALTER cannot express: rebuild the table

Imagine a legacy notes table where severity was free text. The new release wants a strict value set, a normalized legacy spelling, a composite uniqueness rule, and an audit trigger. That is a table redesign, so rebuilding is clearer than trying to force one ALTER statement to do everything.

PRAGMA foreign_keys = OFF;BEGIN IMMEDIATE;CREATE TABLE new_note_rebuild_demo(    note_id      INTEGER PRIMARY KEY,    device_id    INTEGER NOT NULL,    occurred_at  TEXT NOT NULL,    severity     TEXT NOT NULL                 CHECK(severity IN ('info','warning','critical')),    note_text    TEXT NOT NULL CHECK(trim(note_text) <> ''),    UNIQUE(device_id, occurred_at, note_text));INSERT INTO new_note_rebuild_demo       (note_id, device_id, occurred_at, severity, note_text)SELECT note_id,       device_id,       occurred_at,       CASE severity         WHEN 'warn' THEN 'warning'         ELSE severity       END,       trim(note_text)FROM note_rebuild_demo;DROP TABLE note_rebuild_demo;ALTER TABLE new_note_rebuild_demo RENAME TO note_rebuild_demo;CREATE INDEX idx_note_rebuild_device_timeON note_rebuild_demo(device_id, occurred_at);CREATE TRIGGER trg_note_rebuild_nonblankBEFORE INSERT ON note_rebuild_demoWHEN trim(NEW.note_text) = ''BEGIN    SELECT RAISE(ABORT, 'blank note');END;PRAGMA foreign_key_check;COMMIT;PRAGMA foreign_keys = ON;

The critical order is create new → copy/transform → drop old → rename new. SQLite explicitly warns against starting by renaming the old table, because modern rename propagation can modify views, triggers, and foreign-key references in ways that make the later steps wrong.

Verify the database contract, not only the row count

SELECT type, name, sqlFROM sqlite_schemaWHERE tbl_name='note_rebuild_demo' OR name LIKE 'idx_note_rebuild_%'ORDER BY type, name;PRAGMA table_xinfo('note_rebuild_demo');PRAGMA index_list('note_rebuild_demo');PRAGMA foreign_key_check;PRAGMA integrity_check;-- Constraint rejection is part of verification too.INSERT INTO note_rebuild_demo(device_id, occurred_at, severity, note_text)VALUES (1, '2026-08-12T10:00:00Z', 'impossible', 'should fail');

A successful migration should prove structure, data, dependent objects, referential integrity, and expected failure behavior. “The ALTER command returned success” is only one observation.

Checkpoint

Bridge to versioned migrations

Knowing how to change one schema safely is not enough. Applications need an ordered history: version 1 → 2 → 3, each paired with release code, with exactly one process owning migration execution. Lesson 2 turns these SQL operations into a migration protocol.

Authoritative references

• SQLite ALTER TABLE
• SQLite schema table
• SQLite foreign keys
• SQLite PRAGMA reference