MySQL无法删除外键约束中所需的索引。
MySQL无法删除外键约束中所需的索引。
我需要修改现有的数据库以添加一个列。因此,我还想更新UNIQUE字段以包含该新列。我试图删除当前的索引,但是一直收到错误消息:MySQL无法删除外键约束中需要的索引
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): 无法删除索引'AID':外键约束中需要
MySQL在外键约束中需要的索引无法删除的原因是:
在上述例子中,当我们在mytable表中创建一个外键约束时,MySQL会自动创建一个索引来支持该约束。在这个例子中,我们创建了一个名为mytable_ibfk_4的外键约束,它引用了mytable_d表的ID列。MySQL创建了一个名为mytable_ibfk_4的索引来支持这个约束。
当我们尝试删除这个索引时,MySQL会拒绝,因为它是外键约束的一部分。这是因为索引是用于保证外键约束的完整性,如果删除了索引,可能会导致数据不一致。
解决方法是在一个ALTER TABLE语句中同时删除索引并创建新的索引。在上述例子中,我们可以使用以下语句来删除AID索引并创建一个新的唯一索引:
ALTER TABLE mytable DROP KEY AID , ADD UNIQUE KEY AID (AID, BID, CID, DID);
MySQL中出现"Cannot drop index needed in a foreign key constraint"的问题,是由于在删除索引时存在外键约束的关系导致的。下面是解决此问题的步骤:
第一步:
查看外键的名称
SHOW CREATE TABLE <表名>
结果将显示外键的名称,格式如下:
CONSTRAINT `FOREIGN_KEY_NAME` FOREIGN KEY (`FOREIGN_KEY_COLUMN`) REFERENCES `FOREIGN_KEY_TABLE` (`id`)
第二步:
删除外键(包括外键、主键或索引)
ALTER TABLE <表名> DROP FOREIGN KEY <外键名称>
第三步:
删除索引
可以参考这个回答中的步骤1来获取索引的名称:stackoverflow.com/a/201678/2730233
感谢这个答案,帮我省了很多时间。
非常好的答案,运行良好。一个注意事项是,第三步的语法应为 ALTER TABLE 表名 DROP COLUMN 列名;
你展示的答案没有给我任何结果,而这个答案给出了结果。
这是对答案中解决方案的更详细说明,但有两个问题:1)仅删除外键约束是危险的(它存在的原因);2)有更好的方法。我建议阅读这个答案,它详细解释和比较了各种选项。
问题:MySQL无法删除外键约束中所需的索引的原因和解决方法
在MySQL中,您必须先删除外键。MySQL中的外键会自动在表上创建一个索引(关于该主题有一个Stack Overflow问题)。
ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ;
如果删除索引后需要重新添加外键,可以使用以下命令:
ALTER TABLE mytable ADD CONSTRAINT mytable_ibfk_1 FOREIGN KEY (AID) REFERENCES mytable_a (ID) ON DELETE CASCADE;
但是,如果FOREIGN KEY
约束是匿名的,该怎么办呢?可以查看下面的答案:stackoverflow.com/a/54145440/2305119
注意:外键可能不太明显。可以使用以下查询来查找与表和列相关的所有外键:dba.stackexchange.com/questions/102371/...
我很讨厌这是正确答案,但确实是。是的,正如Iaffuste所说,您可能希望在删除索引(或约束)后重新添加外键。
当我首先删除外键,然后尝试从表结构中删除该字段时,出现“Can't DROP FOREIGN KEY userId; check that it exists”的问题。这是什么原因?
有一种更好的方法可以解决这个问题。我建议阅读这篇文章,以深入了解各种选项的优缺点。