MySQL Bugs: #15317: Removing foreign key results in rename error

Bug #15317	Removing foreign key results in rename error
Submitted:	29 Nov 2005 16:18	Modified:	18 Aug 2006 10:12
Reporter:	Allen Mayes	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	Admin 1.1.3	OS:	Windows (Win XP)
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any
Tags:	Object Editors

Description:
Upon dropping a foreign key from a table results in Error number 1025.

How to repeat:
Create two tables
add one column in each table as a primary key (autoincrement, not null, unique)
add one column in one table to hold a foreign key (integer) (this table now has two columns)
add one column in the foreign (second) table as VARCHAR(30)(FK table now has two columns)
back to first table add a foreign key in indices (use any method to relate the key holder in the first table to the primary sey in the second)
Apply changes
On the fist table indices tab highlight the FK_(name)
Press the -
Apply changes--error message results

Thanks for your bug report.  I was able to reproduce this bug using the supplied how to repeat steps.

to delete the FK; change it to a "unique" index, apply, then delete it as normal

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Hi,

this is not a MA bug. This is actually an InnoDB specifics. When you create an FK, it creates/reuses an index. That's why you can see an index on the "Indices" tab. InnoDB doesnt support deletion of FK by removing the index and it cannot of course delete the index at it is needed for the FK. In general there's no way to detect this kind of index to FK relation at the MA level. If you what to remove an FK do it from the "Forein Keys" tab. The correspodning index will be removed automatically.

Regards,
Vlad