时间戳

首页 > 解决方案 > MySQL - 错误 1823：无法将外键约束添加到系统表

mysql - MySQL - 错误 1823：无法将外键约束添加到系统表

问题描述

我正在更新数据库以更正更新和删除时的外键行为。然而，特别是在一张桌子上，这出乎意料地困难。

在大多数桌子上，我一直在执行以下操作，没有任何问题：

  1. 删除外键约束
  2. 删除对应的索引
  3. 添加一个新的、更新的外键

然而，一张桌子造成了一些我不太理解的麻烦。尝试执行上述过程会返回以下错误（我已将数据库名称编辑为db_name）：

SQLSTATE[HY000]: General error: 1025 Error on rename of 
'./<db_name>/#sql-5e1_1c5' to './<db_name>/doctors' 
(errno: 150 - Foreign key constraint is incorrectly formed)
(SQL: alter table `doctors` drop index `doctors_id_unique`)

表的相关部分如下，最初

  UNIQUE KEY `doctors_id_unique` (`id`),
  UNIQUE KEY `doctors_code_unique` (`code`),
  KEY `doctors_updated_by_foreign` (`updated_by`),
  CONSTRAINT `doctors_id_foreign` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `doctors_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我想更改id为主键和外键，所以我执行以下操作：

ALTER TABLE doctors DROP FOREIGN KEY doctors_id_foreign;
ALTER TABLE doctors DROP INDEX doctors_code_unique;
ALTER TABLE doctors DROP INDEX doctors_id_unique;

但是，在第三条语句中，我收到以下错误：

Error on rename of './<db_name>/#sql-5e1_2' to './<db_name>/doctors' 
(errno: 150 - Foreign key constraint is incorrectly formed)

我不知道如何解决这个问题。我尝试先删除updated_by约束和键以及code唯一键，但它不会改变任何东西。我尝试禁用外键检查，但没有雪茄。

我尝试更改用于测试的 SQL 转储。将其更改为以下内容使其按我的预期工作，至少显然：

  PRIMARY KEY (`id`),
  UNIQUE KEY `doctors_code_unique` (`code`),
  KEY `doctors_updated_by_foreign` (`updated_by`),
  CONSTRAINT `doctors_id_foreign` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `doctors_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

但是，我宁愿以编程方式更新表，以便可以更轻松可靠地进行迁移。所以我尝试了一种不同的方法，只是重命名表，将它复制到一个新的键，然后在那里添加键：

SET FOREIGN_KEY_CHECKS=0;
RENAME TABLE `doctors` TO `old_doctors`;
CREATE TABLE `doctors` SELECT * FROM `old_doctors`;
ALTER TABLE `doctors` ADD PRIMARY KEY (`id`);
ALTER TABLE `doctors` ADD UNIQUE INDEX `doctors_code_unique` (`code`);
ALTER TABLE `doctors` ADD CONSTRAINT `doctors_id_foreign` FOREIGN KEY `doctors_id_foreign` (`id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `doctors` ADD CONSTRAINT `doctors_updated_by_foreign` FOREIGN KEY `doctors_updated_by_foreign` (`updated_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE SET NULL;
SET FOREIGN_KEY_CHECKS=1;

现在我收到以下错误：

SQLSTATE[HY000]: General error: 1823 Failed to add the foreign key constraint
'<db_name>/doctors_id_foreign' to system tables (SQL: ALTER TABLE `doctors`
ADD CONSTRAINT `doctors_id_foreign` FOREIGN KEY `doctors_id_foreign` (`id`) 
REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE)

所以我无法让它工作，而且这两个错误似乎都不常见：我找不到任何关于如何处理它们的好信息。

这太费时了，我可能会关闭服务器，转储数据库，手动修复转储并将固定转储导入回来；但是，我真的很想知道这是一些错误还是我只是错过了一些东西。这很有可能，因为我几乎不是一个经验丰富的程序员。

对于上下文：这一切都在 Laravel (5.7.13) 项目中，但是错误发生在使用 Laravel 迁移（使用模式和蓝图）和直接通过 SQL 控制台。MySQL 版本 5.7.22。

tl; dr：我只是无法更改表的外键在更新/删除时的行为，尽管在同一数据库上的各种其他表上成功地这样做了。我尝试了两种截然不同的方法来修复它，并且都返回了（不同的）错误，我在任何地方都找不到解决方案。这是一个错误还是我错过了什么？

标签： mysqllaravel

解决方案

确保没有其他表doctors.id在其外键中引用该列。

如果是，请在删除doctors_id_unique索引之前删除这些外键。

然后在创建主键后恢复外键doctors.id。

推荐阅读