ALTER TABLE not NULL

What if we would like to add a column that should be NOT NULL?

We cannot add a column that is NOT NULL as it would immediately violate the integriry of the table.

We can first add the column. Then update the existing rows, then change the column definition.

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

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;

-- 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;

sqlite3 < examples/alter-table-not-null.sql

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