Rebuild Tables - SQL Programming

Some schema changes are easiest to model as creating a new table, copying accepted rows, and renaming it into place.

Program

Play the script to choose the minimum total copied into the replacement table.

rebuild_table_copy.sql

CREATE TABLE migration_choice AS WITH params(min_total) AS (VALUES ()) SELECT min_total FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER);
INSERT INTO orders VALUES (101, 'Ada', 40), (102, 'Lin', 75), (103, 'Mira', 20);
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);
INSERT INTO orders_new (id, customer, total) SELECT id, customer, total FROM orders WHERE total >= (SELECT min_total FROM migration_choice);
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
SELECT id, customer, total, archived FROM orders ORDER BY id;

CREATE TABLE migration_choice AS WITH params(min_total) AS (VALUES ()) SELECT min_total FROM params;
CREATE TABLE orders (id INTEGER, customer TEXT, total INTEGER);
INSERT INTO orders VALUES (101, 'Ada', 40), (102, 'Lin', 75), (103, 'Mira', 20);
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, customer TEXT, total INTEGER CHECK (total >= 0), archived INTEGER DEFAULT 0);
INSERT INTO orders_new (id, customer, total) SELECT id, customer, total FROM orders WHERE total >= (SELECT min_total FROM migration_choice);
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
SELECT id, customer, total, archived FROM orders ORDER BY id;

replacement table The new table carries the desired primary key, check constraint, and added column.

copy step `INSERT INTO ... SELECT` controls which old rows move into the replacement shape.

rename into place Renaming the replacement table gives callers the original table name again.