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?

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.

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.