Adding NOT NULL column to SQLite table with references

Question

I need to add a column to my existing SQLite table that references a column in another table. I use the command

ALTER TABLE users 
 ADD COLUMN dayChoice_id INTEGER 
             NOT NULL REFERENCES dayChoice(dayChoice_id) DEFAULT 0

And I get the error "Cannot add a REFERENCES column with non-NULL default value". Is that true? How would I add the column then?

possible duplicate of [How to solve "Cannot add a NOT NULL column with default value NULL" in SQLite3?](http://stackoverflow.com/questions/3170634/how-to-solve-cannot-add-a-not-null-column-with-default-value-null-in-sqlite3) — Konstantin V. Salikhov, Jul 02 '14 at 06:27
@KonstantinV.Salikhov, that's kind of the opposite problem that I'm having. In their case, the default was NULL. I have no problem adding the column with NULLs. But I want to make it non-NULL, specifically DEFAULT 0. — Dan Goodspeed, Jul 02 '14 at 06:31
maybe the error is because the column -"dayChoice_id" in the reference table -"dayChoice" is nullable? — st mnmn, Jul 02 '14 at 06:55
dayChoice_id is defined - "dayChoice_id INTEGER PRIMARY KEY". I don't think the primary key is nullable, right? — Dan Goodspeed, Jul 02 '14 at 06:58
@Dan-Goodspeed: In SQLite primary keys can hold NULLs, except for some specific cases: _According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column._ (more details at: http://sqlite.org/lang_createtable.html ) — Googie, Jul 02 '14 at 07:11

score 16 · Accepted Answer · answered Jul 02 '14 at 07:07

You need to disable foreign keys enforcing for the time of executing this ALTER statement, like this:

PRAGMA foreign_keys = 0;
ALTER TABLE users ADD COLUMN dayChoice_id INTEGER
            NOT NULL REFERENCES dayChoice(dayChoice_id) DEFAULT 0;
PRAGMA foreign_keys = 1;

score 7 · Answer 2 · answered Jul 02 '14 at 07:10

From the docs:

If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.

The default value you supplied was not NULL.

It's not compatible with another requirement:

If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

You can probably work around the problem by temporarily disabling foreign key constraints before the ALTER TABLE.

Adding NOT NULL column to SQLite table with references

2 Answers2