I'm trying to optimize the structure of a table. To do so, I've put the columns in the most optimal order possible and then ran this query (simplified):
INSERT INTO table_new ("id", "some_field", "some_other_field") SELECT "id", "some_field", "some_other_field" FROM table_current;
Then I simply rename the table and add all the same indexes and foreign keys.
ALTER TABLE "table_current" RENAME TO "table_old"; ALTER TABLE "table_new" RENAME TO "table_current"; ALTER TABLE "table_current" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;
This has worked great in the past, but in this particular case, there are a LOT of other tables with cascading foreign keys on this table.
So if I recreate and rename, then all the other tables still reference
table_old in their foreign keys instead of the new
How do I do such a table migration like this without having to recreate the foreign keys on every other table as well?