MySQL Removing Some Foreign keys

constraintsforeign-keysMySQLmysql-error-1025

I have a table whose primary key is used in several other tables and has several foreign keys to other tables. 

CREATE TABLE location (
   locationID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
   ...
) ENGINE = InnoDB;

CREATE TABLE assignment (
   assignmentID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   locationID INT NOT NULL,
   FOREIGN KEY locationIDX (locationID) REFERENCES location (locationID)
   ...
) ENGINE = InnoDB;

CREATE TABLE assignmentStuff (
   ...
   assignmentID INT NOT NULL,
   FOREIGN KEY assignmentIDX (assignmentID) REFERENCES assignment (assignmentID)
) ENGINE = InnoDB;

The problem is that when I'm trying to drop one of the foreign key columns (ie locationIDX) it gives me an error.

"ERROR 1025 (HY000): Error on rename"

How can I drop the column in the assignment table above without getting this error?

Best Answer

As explained here, seems the foreign key constraint has to be dropped by constraint name and not the index name.

The syntax is:

ALTER TABLE footable DROP FOREIGN KEY fooconstraint;
Related Topic