I am trying to replace an InnoDB table with a new table, and I want all foreign key references that point to the old table to point to the new table.

So I tried this:

SET foreign_key_checks = 0;
ALTER TABLE foo RENAME foo_old;
ALTER TABLE foo_new RENAME foo;

Unfortunately, even with foreign_key_checks disabled, all references pointing to foo are changed to point to foo_old. Now I am looking for either

  • a way to change the foreign key references back without rebuilding the entire table, OR
  • a way to rename a table without updating foreign key references.

I tried dropping the foreign keys and recreating them, but since the tables are huge, it takes hours. The whole point of replacing the table was to make a schema change with limited downtime.

mysql innodb
Edit
Report