MySQL无法删除外键约束中所需的索引

Question

我需要修改已有的数据库来添加一列。因此，我还想更新唯一字段以包含该新列。 我试图删除当前索引，但是一直收到错误提示：MySQL Cannot drop index needed in a foreign key constraint

CREATE TABLE mytable_a (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;

CREATE TABLE mytable_b (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;

CREATE TABLE mytable_c (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;


CREATE TABLE `mytable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `AID` tinyint(5) NOT NULL,
  `BID` tinyint(5) NOT NULL,
  `CID` tinyint(5) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `AID` (`AID`,`BID`,`CID`),
  KEY `BID` (`BID`),
  KEY `CID` (`CID`),
  CONSTRAINT `mytable_ibfk_1` FOREIGN KEY (`AID`) REFERENCES `mytable_a` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `mytable_ibfk_2` FOREIGN KEY (`BID`) REFERENCES `mytable_b` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `mytable_ibfk_3` FOREIGN KEY (`CID`) REFERENCES `mytable_c` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB;




mysql> ALTER TABLE mytable DROP INDEX AID;
ERROR 1553 (HY000): Cannot drop index 'AID': needed in a foreign key constraint

- user391986

---

假设在mytable表上有一个UNIQUE KEY AID？ - Mike Purcell

当前的热门答案在过去的12年里获得了很多赞同票，但它们都集中在消除错误上。我认为理解和修复潜在问题是很重要的。错误本身是一件好事，就像一个保护措施。我建议阅读这个答案。 - at54321

Answer 1

你需要删除外键。在MySQL中，外键会自动为表创建索引（这个话题有一个相关的SO问题）。

ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ; 

- Brian Fisher

---

删除索引后可能需要重新添加它：ALTER TABLE mytable ADD CONSTRAINT mytable_ibfk_1 FOREIGN KEY (AID) REFERENCES mytable_a (ID) ON DELETE CASCADE; - laffuste

太好了，但是如果我的“FOREIGN KEY”约束条件是匿名的，我该怎么办？ - Pehat

@Pehat 请查看我下面的答案 https://stackoverflow.com/a/54145440/2305119 - thyzz

注意：外键可能不太明显。若要查找与表和列相关的所有外键，您可以使用此查询：https://dba.stackexchange.com/questions/102371/mysql-how-to-check-foreign-keys-related-to-a-table - charlax

有一种更好的方法来完成这个。我建议阅读这篇文章以深入了解各种选择的利弊。 - at54321

Answer 2

步骤一

列出外键（注意它与索引名称不同）

SHOW CREATE TABLE  <Table Name>

结果将向您展示外键名称。

格式：

CONSTRAINT `FOREIGN_KEY_NAME` FOREIGN KEY (`FOREIGN_KEY_COLUMN`) REFERENCES `FOREIGN_KEY_TABLE` (`id`),

步骤二

删除（外键/主键）键。

ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign key name>

第三步

删除索引。

- Abhishek Goel

---

我对这个答案唯一的修改是第一步。我会用这里显示的答案替换它：https://dev59.com/enVC5IYBdhLWcg3wtzut#201678 - Lucio Mollinedo

感谢日志，它节省了我的时间。 - Gaurav Patil

很棒的答案，运行良好。有一个注意点，第三步的语法是 ALTER TABLE table_name DROP COLUMN column_name; - Avery85

@LucioMollinedo 你给出的答案没有给我任何结果，而这个答案却有。 - Isu

这是对被接受答案中解决方案的更详细解释，但它存在两个问题：1）仅仅删除外键约束是危险的（它存在的原因是有意义的）；2）有一种更好的方法。我建议阅读此答案，其中详细解释并比较了各种选项。 - at54321

Answer 3

外键始终需要一个索引。如果没有索引，强制执行约束将需要在引用表中插入或更新每个关联键时对被引用表进行完整的表扫描。这会产生无法接受的性能影响。

• 在创建外键时，数据库会检查是否存在索引。如果不存在，则会创建一个索引。默认情况下，它的名称与约束相同。
• 当只有一个索引可用于外键时，它不可以被删除。如果您真的想删除它，您要么必须删除外键约束，要么先为其创建另一个索引。

- Stefan Mondelaers

---

你提出了其他答案缺乏的理论。 - Dennis

如果你有一个复合唯一索引（唯一约束中的多个列），则不能删除唯一的A-B键，除非你有一个包含A和B的索引。如果出现此错误，则表示另一个表正在使用A或B列的索引，您必须在安全地删除A-B唯一性之前先添加这些索引。 - Robin De Schepper

良好的评论。当使用复合唯一索引时，字段的顺序对于唯一索引来说并不重要，但对于外键可能很重要。在A、B上的唯一索引可以被A上的外键使用，但不能被B上的外键使用。 - Stefan Mondelaers

Answer 4

如果你的意思是你可以做到这一点：

CREATE TABLE mytable_d (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;


ALTER TABLE mytable
ADD COLUMN DID tinyint(5) NOT NULL,
ADD CONSTRAINT mytable_ibfk_4 
      FOREIGN KEY (DID) 
        REFERENCES mytable_d (ID) ON DELETE CASCADE;

 > OK.

但是然后：

ALTER TABLE mytable
DROP KEY AID ;

出现了错误。

---

您可以在一个ALTER TABLE语句中删除索引并创建一个新的索引：

ALTER TABLE mytable
DROP KEY AID ,
ADD UNIQUE KEY AID (AID, BID, CID, DID);

- ypercubeᵀᴹ

Answer 5

因为在外键字段上必须拥有索引，因此您可以在字段“ AID”上创建一个简单的索引。

CREATE INDEX aid_index ON mytable (AID);

然后才能删除唯一索引“AID”

ALTER TABLE mytable DROP INDEX AID;

- Eli DM

---

你的答案和@StefanMondelaers的合并是正确的答案。 - m47730

Answer 6

删除外键是繁琐且有风险的。只需使用新列和新索引名称（例如AID2）创建新索引即可。创建新唯一索引后，您可以轻松删除旧索引。或者，您可以使用上面提供的解决方案，在同一alter table命令中包含“drop index，add unique index”。这两种解决方案都可以使用。

- Moshe Weingarten

---

你能详细说明为什么删除索引是有风险的吗？你能想到可能会出现哪些问题吗？ - KillerKode

@KillerKode 你可能想阅读这个答案以获得详细解释。 - at54321

Answer 7

我认为这是删除索引的简单方法。

set FOREIGN_KEY_CHECKS=0; //disable checks

ALTER TABLE mytable DROP INDEX AID;

set FOREIGN_KEY_CHECKS=1; //enable checks

- Ram E Sh

---

我认为你交换了启用和禁用检查的位置。在顶部，我期望看到FOREIGN_KEY_CHECK=0，而在结尾处是FOREIGN_KEY_CHECK=1。 - romor

我编辑了答案，切换了开关检查并添加了注释。如果可以的话，我会多次投票支持它:D谢谢Ram。 - Dwza

因为这个方法不起作用，所以被踩了。我仍然无法删除唯一索引。 - Martijn Hiemstra

这并不真正起作用。我必须先删除外键。 - Omar Dulaimi

当然，这对这种情况不起作用。 "set FOREIGN_KEY_CHECKS = 0" 只能帮助处理损坏的关系，而不能处理外键本身。 - Константин Константин

Answer 8

对这个问题有更深入的理解不仅有助于找到解决问题的方法，还有助于更好地设计我们的表和索引。一些现有的答案是好的，但其他答案则不完整或明显错误，这对于匆忙读者来说可能是危险的。

重要的初始注意事项：

- 这个问题涉及到一个“UNIQUE”索引，但在这里并不重要。重要的是感兴趣的索引是复合索引（即由2个或更多列组成）。 - 在MySQL中，“KEY”（至少在这个上下文中）是“INDEX”的同义词。

问题所在：

在MySQL中，每个外键约束（FKC）都需要一个索引。并非所有的数据库管理系统都有这样的要求，但实际上，如果你没有这样的索引，一些操作（例如删除被其他表引用的表中的记录）可能会因为引用表足够大而导致性能非常糟糕。这就是为什么在像MySQL这样的一些关系型数据库管理系统中，将这样的索引作为必需的设计决策。

当我们创建外键约束（FKC）时，通常情况下，MySQL会自动创建一个与FK相同的新索引（请记住，主键和外键可以由2个或更多列组成，但在OP的示例中不是这样）。然而，如果已经存在一个可用于FKC的索引，MySQL将不会创建另一个索引。

在OP的示例中，如果我们不计算主键索引，我们有3个索引：AID、BID、CID。第一个是复合索引，其他两个不是。请注意，唯一索引的名称可能会有些误导，因为它与第一列的名称相同。我始终建议为复合索引指定明确的名称，以避免潜在的混淆。所以让我们假设唯一键的名称是ABC，并看看表在起始点上会有哪些索引：

UNIQUE KEY `ABC` (`AID`,`BID`,`CID`),
       KEY `BID` (`BID`),
       KEY `CID` (`CID`),

关键字 ABC 可用于 FKC 上的 AID，因为 AID 是其中的第一个列。这就是为什么MySQL决定不仅在AID上创建额外的索引。但是删除一个被MySQL内部用于满足始终具有索引要求的索引将是不可行的。因此产生了错误。

解决方案

我们首先应该问自己：我们是想单独为AID创建一个显式专用索引，还是想继续使用已有的复合索引。这两种选择都是有效的，取决于我们的需求。如果我们希望在仅查询AID时获得最佳性能，单独的索引可能会稍微更高效，但这会增加一个索引（更多的存储空间、更新速度较慢等）。通常情况下，使用复合索引的优点超过了缺点，但当性能至关重要时，了解可以使用较小的专用索引选项可能会很有用。

选项1：为FKC创建一个（永久的）专用索引

首先，我们为FKC创建专用索引。

CREATE INDEX IDX_AID ON mytable(AID);

请注意，我在这里使用了“IDX_AID”作为名称，但如果我们更喜欢遵循MySQL对此类索引的命名方式，那么在这一点上也可以使用“AID”。在我的例子中，使用“ABC”是可以的，但在OP的情况下不行（尽管通过“RENAME INDEX”可以轻松解决这个问题）。

然后，我们使用新列重新创建唯一索引（让我们称之为“NEW_COL”，并且假设我们希望它位于中间位置/请记住，索引中的列顺序确实很重要/）。

DROP INDEX ABC ON mytable;
CREATE UNIQUE INDEX IDX_ABNC ON mytable(AID, BID, NEW_COL, CID);

现在使用SHOW CREATE TABLE mytable，我们应该会得到以下结果：

UNIQUE KEY `IDX_ABNC` (`AID`,`BID`,`NEW_COL`,`CID`),
       KEY `IDX_AID`  (`AID`),
       KEY `BID`      (`BID`),
       KEY `CID`      (`CID`),

选项2：FKC没有专用索引

我们基本上需要一个解决方法，以避免临时的不一致状态。

方法1：FKC上的临时索引（首选）

这与“选项1”类似，但我们只是在最后删除索引：

CREATE INDEX IDX_TMP ON mytable(AID);
DROP INDEX IDX_ABC ON mytable;  -- here we have IDX_TMP, so no problem
CREATE UNIQUE INDEX IDX_ABNC ON mytable(AID, BID, NEW_COL, CID);
DROP INDEX IDX_TMP ON mytable;  -- not needed anymore

方法二：删除外键约束，然后重新创建

基本上，这个方法的思路是先暂时删除“守卫”（即外键约束），然后在最后重新创建它。

ALTER TABLE mytable DROP FOREIGN KEY <FKC-name>;    
DROP INDEX IDX_ABC ON mytable;  -- no "guard" here to stop us
CREATE UNIQUE INDEX IDX_ABNC ON mytable(AID, BID, NEW_COL, CID);
ALTER TABLE mytable ADD CONSTRAINT <FKC-name> FOREIGN KEY (AID) REFERENCES mytable(AID) ON DELETE CASCADE;

你可以从SHOW CREATE TABLE mytable中找到<FKC-name>。

推荐：

我认为“方法1”更可取，原因如下：

- 稍微简单一些（不需要查找确切的FKC名称）。 - 删除FKC，即使只是很短的时间，也会给别人插入无效值��机会。 - 重新创建FKC时有可能混淆。例如，可能会忘记添加重要属性（如ON DELETE CASCADE），或者意外地放错一个属性。 - 如果由于某种原因我们忘记或未能执行最后一步，使用“方法1”不会造成任何损害，而使用“方法2”则会使数据库处于脆弱状态。

- at54321

Answer 9

可以像下面这样在同一个查询中删除索引和外键：

ALTER TABLE `your_table_name` DROP FOREIGN KEY `your_index`;
ALTER TABLE `your_table_name` DROP COLUMN `your_foreign_key_id`;

- Yann Boyongo

Answer 10

在我的情况下，我删除了外键，但我仍然无法删除索引。这是因为还有另一个表，在同一字段上对此表具有外键。在我删除另一个表上的外键之后，我就可以删除此表上的索引了。

- jav