Add not null DateTime column to SQLite without default value?

Question

It looks like i cant add a not null constraint or remove a default constraint. I would like to add a datetime column to a table and have all the values set to anything (perhaps 1970 or year 2000) but it seems like i cant use not null without a default and i cant remove a default once added in. So how can i add this column? (once again just a plain datetime not null)

Answer 1

Instead of using ALTER TABLE ADD COLUMN, create a new table that has the extra column, and copy your old data. This will free you from the restrictions of ALTER TABLE and let you have a NOT NULL constraint without a default value.

ALTER TABLE YourTable RENAME TO OldTable;
CREATE TABLE YourTable (/* old cols */, NewColumn DATETIME NOT NULL);
INSERT INTO YourTable SELECT *, '2000-01-01 00:00:00' FROM OldTable;
DROP TABLE OldTable;