更新外键以指向不同表的最佳方法?

hjqgdpho  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(281)

我正在清理一个mysql数据库,这个数据库随着时间的推移变得越来越混乱。清理是增量的(也就是说我不想一次迁移所有的表)。
我的偏好是尽可能避免对旧表进行破坏性编辑。例如,我将表文件夹重命名为decommissioned\u folders,然后创建一个新的folders表,相应的记录可以迁移到该表中,而不会影响旧表。
这个数据库中有很多表,其中之一就是表用户。数据库中的许多其他表都有指向表用户的外键。
我想知道:
如果我将表users重命名为decommissioned\u users,然后创建一个新的users表并填充它,那么处理外键的最佳方法是什么?对于数据库中现在包含指向旧的decommissioned\u users表的外键的每个表,是否有(简单的)方法来更新这些外键以指向new users表?
(注意,ids的值不会在表之间更改,因此外键的值将在表之间保持有效。)

zaqlnxep

zaqlnxep1#

有一个简单的方法,但它涉及删除旧的外键和创建新的外键。
下面是一个演示:

mysql> create table foo (id serial primary key);

mysql> create table bar (foo_id bigint unsigned, foreign key (foo_id) references foo(id));

重命名表后,外键引用将自动更新为遵循重命名的表:

mysql> rename table foo to old_foo;

mysql> show create table bar\G

***************************1. row***************************

       Table: bar
Create Table: CREATE TABLE `bar` (
  `foo_id` bigint(20) unsigned DEFAULT NULL,
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `old_foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

创建新表后:

mysql> create table foo like old_foo;

我们可以按以下方式重新定义外键:

mysql> alter table bar 
       drop foreign key bar_ibfk_1, 
       add foreign key (foo_id) references foo(id);

现在该表引用新表:

mysql> show create table bar\G

***************************1. row***************************

       Table: bar
Create Table: CREATE TABLE `bar` (
  `foo_id` bigint(20) unsigned DEFAULT NULL,
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `bar_ibfk_2` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

如果您有许多外键,您可以通过查询information\u schema、tables\u constraints和key\u column\u usage来发现它们的列表。从这个元数据可以构建altertable语句来更改外键。

相关问题