Update foreign key references when doing the SQLite alter table trick

Question

In the SQLite FAQ[1] it is mentioned that SQLite does not have full ALTER TABLE support. In a previous question on StackOverflow [2] a trick is mentioned to accomplish table modifications.

What I would like to know is how to keep FOREIGN KEY references as these are moved to the renamed table which is subsequently deleted. Should I do the same trick with each and every table that has a foreign key relationship with the actual table I am modifying?

[1] http://www.sqlite.org/lang_altertable.html

[2] How do I rename a column in a SQLite database table?

Chengtu

Starting at the end of 2018, PRAGMA foreign_keys=OFF is not enough due to changes of SQLite mainline. You may also need PRAGMA legacy_alter_table=ON , but there are caveats here. – Teratology

Answer 1

Yes, you'll need to do the same "trick". When you rename the referenced table, foreign key constraints still refer to it under its new name. Since SQLite doesn't support "DROP CONSTRAINT", you'll have to rebuild the referencing tables with the corrected foreign key constraints, too.

In fact, you won't be able to drop the old table until you correct the foreign key references. As long as PRAGMA foreign_keys=ON;, SQLite3 won't let you drop a table that still has foreign keys referencing it.

Rowlock

Answer 2

With current versions of SQLite referencing constraints in other tables are not rewritten when foreign key handling has been disabled using PRAGMA foreign_keys=OFF.

Can't say if that behavior was different in 2011.

Heeling

This is not my experience in 2023. – Hubing

Answer 3

The trick that helped me:

alter table some RENAME to some_old;
CREATE TABLE IF NOT EXISTS some (.....)
insert into some select * from some_old;
DROP TABLE IF EXISTS some_old;
alter table some RENAME to some_old;
alter table some_old rename to some;

After this sequence foreign keys are pointing to some and not to some_old

Snow