MySQL Removing Some Foreign keys

Posted by Drew on Stack Overflow See other posts from Stack Overflow or by Drew
Published on 2009-05-08T05:25:54Z Indexed on 2010/03/28 17:43 UTC
Read the original article Hit count: 246

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about foreign-keys