To simulate ALTER TABLE CHANGE COLUMN, one has to recreate the table. So far:

sqlite> create table a (id smallint, primary key (id));
sqlite> create table b (refid integer, value integer, foreign key (refid) references a (id));
…
sqlite> pragma foreign_keys=0;
-- avoid foreign key reference a.id being rewritten to aa.id
sqlite> pragma legacy_alter_table=1;
sqlite> alter table a rename to aa;
sqlite> create table a (id integer, primary key (id));
sqlite> insert into a select * from aa;
sqlite> drop table aa;

Now, I have noticed this can also be done without legacy_alter_table. I am curious to hear some opinions, because this feels like depending on some implicit behavior of the rename mechanism.

sqlite> pragma foreign_keys=0;
sqlite> alter table a rename to aa;
sqlite> create table a (id integer, primary key (id));
sqlite> insert into a select * from aa;
sqlite> drop table aa;
sqlite> .schema b
CREATE TABLE b (refid integer, value integer, foreign key (refid) references "aa" (id));
sqlite> alter table a rename to aa;
sqlite> alter table aa rename to a;
sqlite> .schema b
CREATE TABLE b (refid integer, value integer, foreign key (refid) references "a" (id));

It absolutely depends on implicit behavior of the rename mechanism.

Alter table has been enhanced to also rename the references to a table, such as within triggers, views or externally declared indexes and the like. Using the "legacy" form of alter table, you can change the table name without also updating other DB objects, which is useful if you want to temporarily rename-and-reinstate the table schema, as in your example.

As to the question re our opinion of this, well, I don't know, I opine that it is very interesting. Does that help?