Over the years, we have inherited a number of databases, most utilizing MySQL. Modifying those tables can lead to some odd situations, receiving errors that we shouldn’t, and usually with MySQL, receiving cryptic errors. One recent database upgrade had us scratching our heads for a few minutes –
This database had foreign keys using the format ‘fk_keyName’. All of those indexes had foreign keys, and we had upgraded numerous other tables in this database, dropping some foreign keys and replacing them with an optimized schema. However, upon dropping one particular foreign key, we received the following error:
General error: 1025 error on rename of ‘.\databaseName\tableName’ to ‘.\databaseName\#sql2-aaa-aaa’ (errno:152)
After double-checking the index name and confirming it was correct, we ran ‘SHOW CREATE TABLE’, and noticed that although this foreign key was an index, it did not have a foreign key constraint associated with it. So in this one instance, the foreign key was only an index, most likely a mistake by the original authors.
Hopefully, this will save just a few minutes for someone out there, or save us a few minutes if/when we run into the situation again.