Digital Marketing

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

Popular posts from this blog

Make online money from the Internet