Unable to drop index required by MySQL foreign key constraint

Question

I need to modify my existing database to add a column. Therefore, I also want to update the UNIQUE field to include that new column. I'm trying to drop the current index but I keep getting the error message 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

Answer 1

step 1

List foreign keys (note that they are different from the index names)

SHOW CREATE TABLE  <表名>

The results will show the foreign key names.

Format:

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

Step 2

Delete (foreign key/primary key/key)

ALTER TABLE <表名> DROP FOREIGN KEY <外键名称>

Step 3

Delete index.

Answer 2

You must delete the foreign key. In MySQL, foreign keys automatically create an index on the table (this SO question is about this topic).

ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ;