MySQL – The Name of the Table

Beware when renaming a table if there are other tables around that have foreign keys on the renamed table.

Following case: we want to exchange some table against a better structured one but for some reason do not want to run ALTER TABLE on the original table. So the way out is to create a new table with the new structure, transfer all the necessary data from the original table into it and exchange the rename the tables by renaming them. You will need this when having to alter large tables in a 24/7 running application when you cannot afford running hours long ALTER TABLE queries. See MySQL documentation, ALTER TABLE will block write access until table structure is changed and that can take a long time for large tables.

Example code:test setup

Now we do the following:

CREATE TABLE data_base_new (
id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'the id',
data VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'the data',
more_data VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'some more data',
last_updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT '0000-00-00 00:00:00' COMMENT 'date of last update',
PRIMARY KEY ( id ),
INDEX `idx_last_updated` ( last_updated )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO data_base_new ( id, data, last_updated ) SELECT a.id, a.data, a.last_updated FROM data_base a;

Switch from old to new table:

RENAME TABLE data_base TO data_base_old;
RENAME TABLE data_base_new TO data_base;

If you did not remember the other table data_extended until now that had foreign keys on
table data_base, you will notice when trying to DROP TABLE data_base_old .

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails


SHOW CREATE TABLE data_extended;

explains why:
SHOW CREATE TABLE

The foreign key constraints stick to their original table, no matter if you change the name. Nasty one if you now are running your application with the wrong foreign key constraint 😉

I did not find another way until now as to drop the foreigns keys, then rename the tables and then set the foreign keys again. Or duplicate all related tables, not only the master table.

If you need to know: No, we did not frak our live database up, but we sometimes think about what we night need in the future and this was part of finding out bad things beforehand 🙂

This entry was posted in howto, mysql. Bookmark the permalink.

Comments are closed.