Deleting A Column Used As A Foreign Key In MySQL

I came across a rather cryptic MySQL error message today while working on a new database.

I had a foreign key I wanted to remove from a table due to a change in the database structure, however when I tried to remove it I kept getting the following error.

mysql> alter table band drop foreign key track_id;
ERROR 1025 (HY000): Error on rename of './rob/band' to './rob/#sql2-b0c-3588d' (errno: 152)

I thought this was a permissions problem to start with on the file structure, but in reality it’s InnoDB complaining that I can’t drop the column I’m using as the foreign key.

The solution is to drop the key first, before dropping the column.

But how do you find the name of the key?

Well for that you need to use show create table tablename, in my case tablename was band.

Running this on my code I found my key (as show in this snippet of SQL)…

...
PRIMARY KEY (id),
KEY track_id (track_id),
CONSTRAINT id_trackkey FOREIGN KEY (track_id) REFERENCES track (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So I can see this is id_trackkey. The following SQL deleted the key and the column.

alter table band drop foreign key id_trackkey;
alter table band drop track_id;

Voila, the foreign key and the column are now deleted.

Leave a Reply