Here are Rob's Blog entries for November 2007.
Blog entries for other months can be found in the main blog index.
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.