When adding a column with a foreign key relation:
ALTER TABLE posts ADD COLUMN author TEXT NOT NULL DEFAULT '01H77V720CVTMAD2Q4GTQYJDNB' -- valid in my database. REFERENCES users (id);
SQLite (version 3.39.5) tells me:
Cannot add a
REFERENCEScolumn with non-
Trying without a non-
NULL default value (which I didn’t expect to work):
ALTER TABLE posts ADD COLUMN author TEXT NOT NULL REFERENCES users (id);
and it tells me:
Cannot add a
NOT NULLcolumn with default value
It is not possible to use the
ALTER TABLE ... ADD COLUMNsyntax to add a column that includes a
REFERENCESclause, unless the default value of the new column is
NULL. Attempting to do so returns an error.
This means I need to follow the procedure “Making Other Kinds Of Table Schema Changes”1. In essence: create a new table; populate; drop old and rename new to old; recreate all indexes, triggers, and views. For the database that backs allenap.me this is fine, but I am nevertheless pining for PostgreSQL in which I’m pretty sure this is covered. Holding me back is the higher operational complexity of PostgreSQL; for my needs the trade-off is presently still in favour of SQLite.
None of the docs are versioned on the sqlite.org website, hence links to specific parts of the SQLite documentation will inevitably rot. Sorry.