I'm about to drop an index from a mySQL INNODB table. There are references defined already. When I try to alter it, it says:

#1025 - Error on rename of 'X/#sql-2a5f_219828' to 'X' (errno: 150)

Which at first seems logical, since there are relations, it can't be renamed. But even if I disable foreign key checking:

SET foreign_key_checks = 0;


I get the same! I don't want to drop ALL foreign key definitions, drop that index, and define them again! Another solution?


Create a new index before dropping the old one.

  • what has a new index has to do with the old one? – deeped Aug 11 '11 at 22:50
  • My answer may be incorrect if it does not help for you. I recently faced a situation where I had a foreign key referring to a column in another table and wanted to change/re-create the index for that column. I was not able to get rid of the old index, but after I first created a new index (basically a duplicate of the original index) I was able to drop the old one. Maybe the error message was different though and this is not related... Sorry about the noise. – snap Aug 11 '11 at 23:51

the problem was there was already an index on the foreign key field :) and I didnt know that foreign key is also an index. Anyway I ripped all my hair out in the end, I had to drop all indexes to get rid of it. I will never use innoDB again

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.