Changing a schema changes both structure and the interpretation of stored facts. A migration succeeds only when the destination constraints hold, existing identities and values survive according to policy, dependent objects are handled, and failure leaves a usable state. “The SQL ran” and “the row count stayed equal” are much weaker claims.
The bounded lab replaces products.label with products.name, adds a constrained default currency and rejects negative prices. Unknown prices remain NULL. A tempting COALESCE(price_cents,0) makes the copy look simpler but changes unknown into free. Likewise, rebuilding integer IDs from row order preserves a count while breaking every identity. Write the preservation rules before writing the copy statement.
SQLite supports several direct ALTER TABLE operations, including renaming tables and columns. More involved changes can require a replacement table with explicit constraints. CREATE TABLE AS SELECT is useful for copying values, but it does not recreate this schema's keys, defaults and checks. Define the destination deliberately, then copy an explicit column list so a changed column order cannot silently remap values.
CREATE TABLE products(id INTEGER PRIMARY KEY,label TEXT NOT NULL,price_cents INTEGER);
INSERT INTO products VALUES(4,'Pen',NULL),(9,'Book',0);
BEGIN;
CREATE TABLE products_new(id INTEGER PRIMARY KEY,name TEXT NOT NULL,
price_cents INTEGER CHECK(price_cents>=0),currency TEXT NOT NULL DEFAULT 'USD');
INSERT INTO products_new(id,name,price_cents)
SELECT id,label,price_cents FROM products;
DROP TABLE products;
ALTER TABLE products_new RENAME TO products;
COMMIT;
SELECT id,name,price_cents,currency FROM products ORDER BY id;The explicit transaction covers creation, copy, replacement and index reconstruction. If the copy encounters a negative price, SQLite raises a CHECK error. The modeled client then rolls back the active transaction. Earlier autocommit DDL would not become pending again; this is why the boundary must begin before creating the replacement. A statement error is not a general promise that every earlier statement has been undone.
Dropping a table removes its attached index. The lab therefore recreates products_name on the new name column. Real databases can also have incoming foreign keys, views and triggers. Their dependencies change the safe migration sequence. The fixture deliberately has none on products and keeps foreign-key enforcement enabled; do not paste this bounded swap into an arbitrary live database. The official ALTER TABLE guide explains the broader review, and deployment requires backups, version coordination and representative validation beyond this exercise.
Verification compares actual IDs, exact text, NULLs and prices, preserves an unrelated audit log, checks the replacement's default with a real insert, and checks index columns through trusted metadata inspection. A negative-row fixture verifies the old label schema and old index after rollback, with no leftover products_new. These are native one-connection SQLite checks, not evidence about production WAL behavior or a crash during file-backed deployment.
Use the fill to practice an explicit mapping, and the bug hunts to distinguish a broken boundary from a semantically wrong backfill. Both can destroy correctness even when a happy-path row count looks reasonable.