Foreign keys issue when swapping MySQL tables by renaming
When you swap MySQL tables by renaming, you need to manually update all the affected foreign keys.
Use Sakila as example:
mysql> SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'address' and TABLE_SCHEMA = 'sakila';
+------------+-------------+---------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+---------------------+-----------------------+------------------------+
| customer | address_id | fk_customer_address | address | address_id |
| staff | address_id | fk_staff_address | address | address_id |
| store | address_id | fk_store_address | address | address_id |
+------------+-------------+---------------------+-----------------------+------------------------+
3 rows in set (0.01 sec)
mysql> insert into addr select * from address limit 8;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> RENAME TABLE address TO tmp_table,
-> addr TO address,
-> tmp_table TO addr;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'address' and TABLE_SCHEMA = 'sakila';
Empty set (0.00 sec)
mysql> SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'addr' and TABLE_SCHEMA = 'sakila';
+------------+-------------+---------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+---------------------+-----------------------+------------------------+
| customer | address_id | fk_customer_address | addr | address_id |
| staff | address_id | fk_staff_address | addr | address_id |
| store | address_id | fk_store_address | addr | address_id |
+------------+-------------+---------------------+-----------------------+------------------------+
3 rows in set (0.00 sec)
mysql>
You cannot modify information_schema to alter the foreign keys, you should use the ALTER table for that.
Comments
Post a Comment