SQLite Table Rebuilds Need More Than a Copy Statement

SQLite supports useful ALTER TABLE operations, but more involved schema changes may require creating a replacement table and copying data. The danger is treating the table's rows as the entire schema. Indexes, triggers, views, foreign keys and constraints are also part of the application's contract.

A safe rebuild is an inventory, transformation and verification workflow. Rehearse it on a disposable copy before touching the real database.

The concrete problem

The example changes a permissive task state into an explicitly constrained domain. It is a simplified rebuild for a table with no external references; a table participating in foreign keys requires the full documented procedure and ordering. Back up the database first through an appropriate SQLite mechanism.

Figure: Inventory dependent objects → Copy into constrained table → Restore objects and verify.

A worked example

-- First inspect dependencies and existing values.
SELECT type, name, sql FROM sqlite_schema
WHERE tbl_name='tasks';
SELECT state, COUNT(*) FROM tasks GROUP BY state;
BEGIN IMMEDIATE;
CREATE TABLE tasks_new (
  id INTEGER PRIMARY KEY,
  state TEXT NOT NULL CHECK(state IN ('queued','running','done'))
);
INSERT INTO tasks_new(id,state)
SELECT id,state FROM tasks;
DROP TABLE tasks;
ALTER TABLE tasks_new RENAME TO tasks;
-- Recreate required indexes/triggers here from reviewed definitions.
PRAGMA foreign_key_check;
COMMIT;

Why the behavior matters

The copy is also a validation step: values that violate the new CHECK constraint should stop the migration rather than being silently coerced into a fallback state. Decide explicitly whether old values need mapping, rejection or manual repair. Retain identifiers so relationships and external references do not change unexpectedly.

Before committing, recreate dependent objects using inspected definitions. sqlite_schema is an inventory source, not a license to execute unreviewed text indiscriminately. If the table is referenced by foreign keys, follow SQLite's general ALTER procedure, including the documented foreign_keys handling outside the transaction when applicable.

Failure modes and design tradeoffs

Renaming the old table first can have unexpected effects on triggers, views and foreign-key references. Follow the documented replacement-table sequence instead of improvising a rename chain. SQL behavior also evolves by SQLite version, so pin and test the version shipped by the actual application runtime.

A failed statement does not mean the surrounding application has correctly rolled back and reopened every connection. The migration runner must handle errors, retain the backup and avoid starting normal application traffic against a partially handled migration.

What to verify before adoption

Compare row counts, key sets, constraint violations, index definitions and trigger behavior before and after. Run representative application reads and writes, including expected rejections. Test rollback on a deliberately invalid state. Only record completion after reopening the database through the application and confirming the new schema version.

Primary reference

• SQLite documentation. Verify the exact deployed version and driver behavior before using the example in a production runbook.