SQLite Forum

Bug in ALTER TABLE ADD COLUMN
Login
When you try to add a new column to an empty table using `ALTER TABLE`, and this column is defined as `NOT NULL`, SQLite gives an error: `Error: Cannot add a NOT NULL column with default value NULL`. I think this is not correct because an empty table does not need a default value for the `ADD COLUMN` to succeed. Creating a table with a `NOT NULL` column is possible.

Example creating a table with `NOT NULL` columns, which succeeds:

```
CREATE TABLE test (Column1 INT NOT NULL, Column2 INT NOT NULL);
```


Example creating the same table in two steps, which fails:
```
CREATE TABLE test (Column1 INT NOT NULL);
ALTER TABLE test ADD COLUMN Column2 INT NOT NULL;
```

Both operations should be equivalent for empty tables. For non-empty tables the error message is as expected.