SQLite Forum

alter table "table" add column "name" varchar not null throws Cannot add a NOT NULL column with default value NULL
Login
Can't answer all the questions, but let me see if I can explain some of what's going on.

When you do an ALTER TABLE ADD COLUMN, all that gets updated in the file is the sqlite_master table. No changes are made to any of the actual records on the disk. That makes it super quick even if you have billions of records in the table.

The on disk format for a record has all field values stored in the order they are in the table. When you come along and read a record it says "here are the 5 data types, followed by the 5 values." If SQLite is reading from a table that actually has 6 columns, it gets that data and uses it for fields 1-5. Then it says "well, there's nothing here for column 6, so let's use its DEFAULT value"

So DEFAULT in SQLite is both the normal "use this value on insert if it's not specified for this field" but it's also "use this value when reading from disk if there's nothing there for this field"

(I'm sure I'll be corrected if I explained it wrong)