MySQL Cannot drop index needed in a foreign key constraint

Question

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 gt  ALTER TABLE mytable DROP INDEX AID  ERROR 1553  HY000   Cannot drop index  AID   needed in a foreign key constraint

User · Accepted Answer

You have to drop the foreign key   Foreign keys in MySQL automatically create an index on the table  There was a SO Question on the topic    ALTER TABLE mytable DROP FOREIGN KEY mytable ibfk 1

User · Answer

Step 1  List foreign key   NOTE that its different from index name    SHOW CREATE TABLE   lt Table Name gt    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  lt Table Name gt  DROP FOREIGN KEY  lt Foreign key name gt    Step 3  Drop the index

User · Answer

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

User · Answer

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

User · Answer

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     gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql