ALTER TABLE error

Question

Can someone explain to me why I am receiving the following error?

I want to rename the column "exerciseID" to "ID" in a mysql table using the following syntax.

ALTER TABLE  `exercises` CHANGE  `exerciseID`  `ID` INT( 11 ) NOT NULL AUTO_INCREMENT

However I receive the following error:

MySQL said: 

#1025 - Error on rename of './balance/#sql-de_110e' to './balance/exercises' (errno: 150)

Any suggestions would be much appreciated

Answer 1

i think it might be a multi step process.

1. add a new column,
2. copy the data over from the original column
3. drop the old column

Answer 2

just having a quick search in google, and it looks like you are referencing the column in a foreign key - which is preventing the rename.

afraid i'm not sure how you'd resolve the problem, as I haven't used foreign keys in MySQL all that much

Answer 3

I would check to see if you have any foreign key references to that column. If so, you may need to remove the foreign relationships that you have defined for that column, then rename, then place your foreign key relationships back in place with the new column name.

I think MySQL is getting hung up on the fact that when you rename, the FK relationships are no longer valid and it is throwing an error.

EDIT: Confirmed FK Rename in MySQL

You will need to do something like this:

alter table yourTable drop foreign key yourID