MySQL Removing Some Foreign keys

Question

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?

Answer 1

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; 

Answer 2

The foreign keys are there to ensure data integrity, so you can't drop a column as long as it's part of a foreign key. You need to drop the key first.

I would think the following query would do it:

ALTER TABLE assignmentStuff DROP FOREIGN KEY assignmentIDX;