MySQL Cannot drop index needed in a foreign key constraint

Question

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 ; 

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.

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);

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.