ALTER TABLE in transaction

Even better, do all the changes inside a transaction. That way they are either all implemented, or nothing changes.

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

INSERT INTO person (name) VALUES ('Joe');
INSERT INTO person (name) VALUES ('Jane');

SELECT * FROM person;

-- ALTER TABLE person ADD COLUMN phone TEXT NOT NULL;
-- Error near line 11: Cannot add a NOT NULL column with default value NULL


BEGIN TRANSACTION;
ALTER TABLE person ADD COLUMN phone TEXT;
UPDATE person SET phone = '123' WHERE name = 'Joe';
UPDATE person SET phone = '567' WHERE name = 'Jane';
SELECT * FROM person;

ALTER TABLE person ALTER phone SET NOT NULL;
COMMIT;

-- INSERT INTO person (name) VALUES ('Mary');
-- Error near line 21: NOT NULL constraint failed: person.phone

INSERT INTO person (name, phone) VALUES ('Mary', '890');
SELECT * FROM person;

$ sqlite examples/alter-table-in-transaction.sql

1|Joe
2|Jane
1|Joe|123
2|Jane|567
1|Joe|123
2|Jane|567
3|Mary|890