Adding a column with REFERENCES in SQLite 3

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 REFERENCES column with non-NULL default value

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 NULL column with default value NULL

Those errors appear to be somewhat contradictory. Once you know what to look for there is an explanation in the docs¹:

It is not possible to use the ALTER TABLE ... ADD COLUMN syntax to add a column that includes a REFERENCES clause, 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”¹. 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.

---