mysql - 由于 "incorrectly formed foreign key constraint"错误，无法更改主键

Question

我有一个具有以下架构定义的表:

CREATE TABLE `currency` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` char(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_UNIQUE` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

我想要的是删除 id 列并将 code 作为新的主键。并且其他一些表具有该表的外键。我尝试了以下命令但失败了:

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `currency` CHANGE COLUMN `id` `id` INT(11) NOT NULL, DROP PRIMARY KEY;
ALTER TABLE currency ADD PRIMARY KEY (code);
SET FOREIGN_KEY_CHECKS=1;

MySQL 抛出以下异常:

[ERROR in query 2] Error on rename of './db/#sql-849_1' to './db/currency' (errno: 150 - Foreign key constraint is incorrectly formed) Execution stopped!

Answer 1

错误

Error on rename of ... errno: 150 - Foreign key constraint is incorrectly formed)

发生这种情况是因为您试图删除引用的主键，即使您使用 SET FOREIGN_KEY_CHECKS=0;

禁用了外键约束检查

禁用外键检查将允许您临时删除 currency 表中的一行或在外键表中添加无效的 currencyId，但不会删除主表键。

更改已被其他表引用的 PRIMARY KEY 并不简单，因为您可能会失去表之间的参照完整性和数据之间的关系。为了保留数据，您需要一个过程，例如:

• 为每个外键表添加一个新的外键列(code)
• 通过更新映射来自先前 currencyId 的 code 外键
• 删除现有的外键
• 删除旧的 currencyId 外键列
• 删除所有 FK 后，更改 currency 表的主键
• 根据新的code列重新建立外键

下面的代码无需禁用 FOREIGN_KEY_CHECKS 即可执行此操作，但需要为所有引用 currency 的表重复外键映射/删除/重新创建步骤:

-- Add new FK column
ALTER TABLE FKTable ADD currencyCode char(3) 
                        CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
-- Map FK column to the new Primary Key
UPDATE FKTable
  SET currencyCode = (SELECT `code` FROM currency WHERE id = FKTable.currencyId);
-- Drop the old foreign key + column
ALTER TABLE FKTable DROP FOREIGN KEY FKTable_Currency;
ALTER TABLE FKTable DROP COLUMN currencyId;
-- Once the above is done for all FK tables, drop the PK on currency
ALTER TABLE `currency` CHANGE COLUMN `id` `id` INT(11) NOT NULL, 
                                                      DROP PRIMARY KEY;
ALTER TABLE currency ADD PRIMARY KEY (`code`);
ALTER TABLE FKTable ADD CONSTRAINT FKTable_Currency2
          FOREIGN KEY (currencyCode) REFERENCES currency(`code`);

SqlFiddle here