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 1025 (HY000): Error on rename" error.

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

Answer 1

You can not drop the foreign key column because it is being referenced from the table assignmentStuff. So you should first drop the foreign key constraint assignmentStuff.assignmentIDX.

A similar question has already been asked here. Check also here for more info.

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 KEY assignmentIDX;

Answer 3

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

Hope this helps.