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?

Answer 1

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;

Answer 2

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.