Mysql: Renaming an InnoDB table without updating foreign key references to it?

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.

 Answers

Old questions, but following is a possible way around. Basically move the data rather than renaming the tables. You need to of course make sure the new data adhere to the foreign key rules.

SET foreign_key_checks = 0;
CREATE TABLE IF NOT EXISTS foo_old LIKE foo;
INSERT INTO foo_old SELECT * FROM foo;
TRUNCATE foo;
INSERT INTO foo SELECT * FROM foo_new;

Make sure you run it as one query so the foreign_key_checks applies to the whole thing. Hope this helps.

---

On MySQL 5.6 with innodb_file_per_table=ON allows you to swap the table spaces on the fly. This can't be done completely using SQL as file operations need to be performed separately. First prepare the foo_new table to be copied and drop the foo data:

SET foreign_key_checks = 0;
ALTER TABLE foo DISCARD TABLESPACE;
FLUSH TABLES foo_new FOR EXPORT;

At this point you need to copy the relevant InnoDB files to correct name. Files are stored in your data directory. On Debian, for example, they are by default in /var/lib/mysql/yourdatabase and files are foo_new.ibd, foo_new.cfg and foo_new.frm. Copy them to foo.ibd, foo.cfg and foo.frm, respectively. For example:

$ cp foo_new.ibd foo.ibd
$ cp foo_new.frm foo.frm
$ cp foo_new.cfg foo.cfg

Pay attention that MySQL has access to the new files (e.g. they have correct owner, access rights). Once done you can import the table again and enable foreign keys:

UNLOCK TABLES;
ALTER TABLE foo IMPORT TABLESPACE;
SET foreign_key_checks = 1;

This only copies foo_new to foo. Repeat the steps if you need to copy foo to foo_old.

---

I found a way around this... you just drop the source table instead of renaming it.

For this example, we'll call the table 'mytbl'.

1. create copy of the source table, e.g. 'mytbl_new'
2. copy data into the new table
3. drop the source table 'mytbl'
4. rename 'mytbl_new' to 'mytbl'

The only downside is you can't keep a backup of your original table, but you could mysqldump it before hand. Alternatively, you could create an additional table copy if you wanted a verbatim copy of the original table.