SQLite Forum

Bug in ALTER TABLE ADD COLUMN
Login
The error you got is correct for the general case: you can't add a `NOT NULL` column without giving a `DEFAULT` clause, because all of the pre-existing rows effectively get set to `NULL`, and you just told SQLite that column can't be `NULL`.

There is an edge case where your `ALTER TABLE` statement *could in principle* work, that being when the table is empty, which is also why your one-step creation works. SQLite currently doesn't have a special-case check for this condition, so you fall into the general case above.

All of this suggests a possible workaround:

       ALTER TABLE test ADD COLUMN Column2 INT NOT NULL DEFAULT 0;

Whether that particular default is appropriate to your application is another question entirely.