Data Jelly

So today I ran across a very cryptic error message, although after poking around for a bit the solution ended up being rather simple. I was trying to drop a unique constraint from my database as follows:

ALTER TABLE abc DROP KEY xyz

And the error I received was something like this:

ERROR 1025 (HY000): Error on rename of './test/#sql-2fa8_1' to './test/abc' (errno: 150)

(The error may not be exact because I couldn't reproduce the problem again. Go figure.)

After searching the 'net, I found a couple articles outlining similar issues:

• MySQL's ERROR 1025 explained
• Problems with ALTER TABLE
• Incorrect error when deleting a foreign key with incorrect constraint name

Solution

In the end, my problem was solved by dropping the foreign keys on the given table, dropping the unique constraint, and then adding the foreign keys back in. This solution seemed a little strange to me, but after thinking it over, my guess is that the unique constraint may have also been serving to help enforce certain foreign keys.

Still, I wonder why MySQL couldn't automatically handle this scenario?