[mysql] MySQL Cannot drop index needed in a foreign key constraint

I need to ALTER my existing database to add a column. Consequently I also want to update the UNIQUE field to encompass that new column. I'm trying to remove the current index but keep getting the error 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

In my case I dropped the foreign key and I still could not drop the index. That was because there was yet another table that had a foreign key to this table on the same fields. After I dropped the foreign key on the other table I could drop the indexes on this table.

Step 1

List foreign key ( NOTE that its different from index name )

SHOW CREATE TABLE  <Table Name>

The result will show you the foreign key name.

Format:

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

Step 2

Drop (Foreign/primary/key) Key

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

Step 3

Drop the index.

drop the index and the foreign_key in the same query like below

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

I think this is easy way to drop the index.

set FOREIGN_KEY_CHECKS=0; //disable checks

ALTER TABLE mytable DROP INDEX AID;

set FOREIGN_KEY_CHECKS=1; //enable checks

If you mean that you can do this:

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.

But then:

ALTER TABLE mytable
DROP KEY AID ;

gives error.

You can drop the index and create a new one in one ALTER TABLE statement:

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

Because you have to have an index on a foreign key field you can just create a simple index on the field 'AID'

CREATE INDEX aid_index ON mytable (AID);

and only then drop the unique index 'AID'

ALTER TABLE mytable DROP INDEX AID;

If you are using PhpMyAdmin sometimes it don't show the foreign key to delete.

The error code gives us the name of the foreign key and the table where it was defined, so the code is:

ALTER TABLE your_table DROP FOREIGN KEY foreign_key_name; 

A foreign key always requires an index. Without an index enforcing the constraint would require a full table scan on the referenced table for every inserted or updated key in the referencing table. And that would have an unacceptable performance impact. This has the following 2 consequences:

• When creating a foreign key, the database checks if an index exists. If not an index will be created. By default, it will have the same name as the constraint.
• When there is only one index that can be used for the foreign key, it can't be dropped. If you really wan't to drop it, you either have to drop the foreign key constraint or to create another index for it first.