☰

如何删除 MySQL 中孤立的外键约束？

tag-icon • tag-icon mysql innodb mysql5.5 foreign-key
如何删除 MySQL 中孤立的外键约束？

使用 MySQL 5.5。我有一张无法添加外键的表：

ALTER TABLE `SOURCE_TABLE` 
    ADD CONSTRAINT `ConstraintFK`
        FOREIGN KEY (`otherTableID`)
        REFERENCES `OTHER_TABLE` (`id`)
        ON DELETE SET NULL
        ON UPDATE CASCADE;

MySQL 返回以下错误：

Error Code: 1005. Can't create table 'my_schema.#sql-4c0c_b6fc8ca' (errno: 121)

看看SHOW ENGINE INNODB STATUS我得到：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
220523 16:34:36 Error in foreign key constraint creation for table `my_schema`.`#sql-4c0c_b6fc8ca`.
A foreign key constraint of name `my_schema`.`ConstraintFK`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

当然，这个模式中没有定义名为 ConstraintFK 的约束，我检查了信息模式和输出SHOW CREATE TABLE SOURCE_TABLE。后者显示外键的索引存在，但外键约束似乎不存在：

-- only relevant info shown
CREATE TABLE `SOURCE_TABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `otherTableID` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ConstraintFK_idx` (`otherTableID`)
) ENGINE=InnoDB AUTO_INCREMENT=4089 DEFAULT CHARSET=utf8;

CREATE TABLE `OTHER_TABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=58108 DEFAULT CHARSET=utf8;

事实上，如果我尝试放弃这个限制：

ALTER TABLE `SOURCE_TABLE`
   DROP FOREIGN KEY `ConstraintFK`;

我得到：

Error Code: 1025. Error on rename of './my_schema/SOURCE_TABLE' to './my_schema/#sql2-4c0c-b6fc8ca' (errno: 152)

我查看了文件系统，没有发现重命名表失败的原因。

不幸的是，尝试查询信息模式没有帮助：

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
    WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
    AND information_schema.TABLE_CONSTRAINTS.CONSTRAINT_NAME = 'ConstraintFK';

返回一个空集，而：

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
    WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' 
    AND information_schema.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA = 'my_schema';

返回 my_schema 中的所有外键约束，我也可以从SHOW CREATE TABLE语句中获取这些约束，但没有 ConstraintFK 的迹象...

看着这和这，我怀疑过去发生了一些事情导致该外键被孤立：事实上，这个 SOURCE_TABLE 前段时间被重命名了，我很确定我试图添加的外键过去就在那里。建议的解决方法是删除架构并从转储文件重新创建。我还可以尝试其他不涉及删除此架构的方法吗？它非常庞大，停机时间也很重要。

答案1

最后，我被迫转储整个模式并用另一个名称恢复它，然后删除具有旧名称的模式（并包含有问题/损坏的SOURCE_TABLE）。

确实，我发现这个答案来自 dba.stackexchange.com 描述了一种可能解决这个问题的方法，而且它确实似乎有效......直到某个时候：

  • 重新创建表SOURCE_TABLE，使其结构完全相同，但名称与重命名前的名称相同；确保还指定外键（否则 MySQL 将返回错误 150），但使用不同的名称（否则 MySQL 将返回错误 121）。还要确保索引名称反映外键约束的新名称。因此，在我的示例中，使用旧名称重新创建的表必须具有相同的外键（如上所述），但名称不同（例如，ConstrFK而不是ConstraintFK）；索引也必须命名ConstrFK_idx（否则您将再次收到错误 121）
  • 如果以上所有条件都满足，MySQL 应该允许你使用旧名称创建表
  • 然后你可以删除刚刚创建的表
  • 然后返回SOURCE_TABLE并重试以根据需要创建ConstraintFK...现在它应该可以工作了

然而，在我使用 MySQL 5.5.62（Percona Server）的情况下，DBMS 在最后一步完全崩溃了，就在我看到外键创建确实成功后的几秒内。重新启动时（崩溃恢复完成后），表处于不一致状态（它导致 MySQL Workbench 崩溃，而我的应用程序似乎抱怨它SOURCE_TABLE不再存在）。因此，为了摆脱这种可怕的情况，我确实被迫转储整个模式（我使用的是最新的副本，它仍然完好无损SOURCE_TABLE），重新导入为新模式，然后删除有问题的表的模式，摆脱所有这些麻烦……当然，这至少需要几个小时的停机时间……

相关内容