Add non null DateTime column to SQLite without default value?

Question

It seems like I cannot add a null constraint or remove the default constraint. I would like to add a datetime column to the table and set all the values ​​(maybe 1970 or 2000), but it looks like I cannot use null without the default value, and I cannot delete the default value after adding. can i add this column? (again just a simple datetime is not null)

Answer 1

Instead of using ALTER TABLE ADD COLUMN create a new table with an extra column and copy the old data. This will free you from ALTER TABLE constraints and allow you to have a NOT NULL 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; 

Edit: The official SQLite documentation for ALTER TABLE now warns against the above procedure, as it "may corrupt links to this table in triggers, views, and foreign key constraints." A safe alternative is to use a temporary name for the new table, for example:

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