Alter Table add Not Null Column with no Default Value

Databases
1

How do you get around adding a not null column with no default value when you have existing records in Sqlite?

ALTER TABLE MyTable ADD new_column TEXT NOT NULL

returns “Cannot add a NOT NULL column with default value NULL”

Thanks

2

One way would be to recreate the table and import the data into it.

3

add a default value to the alter

ALTER TABLE MyTable ADD new_column TEXT NOT NULL default “”

see
http://sqlite.org/lang_altertable.html
http://sqlite.org/syntax/column-def.html
http://sqlite.org/syntax/column-constraint.html

5

Is there a default value you CAN assign ?

If not then you’re not going to be able to add a “not null” constraint since when you alter the table, conceptually, EVERY existing row immediately gets altered to have the new column and it MUST have some value (the default) if you say “NOT NULL”

6

Thanks guys, these were both helpful.