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

  • Z时代
  • 2024-01-10
  • 分类：问答

我需要更改现有数据库以添加列。因此，我也想更新UNIQUE字段以包含该新列。我正在尝试删除当前索引，但不断收到错误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

回答：

您必须删除外键。MySQL中的外键会在表上自动创建索引（该主题上有一个SO问题）。

ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ;

以上是 MySQL无法删除外键约束中所需的索引 的全部内容， 来源链接： utcz.com/qa/421801.html

回到顶部