Bug #69937 Cannot delete index that belongs to foreign key when another index exists
Submitted: 6 Aug 2013 9:58 Modified: 14 Jan 2014 3:39
Reporter: Michal Koscielny Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:6.0.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: foreign key index delete remove impossible cannot unable

[6 Aug 2013 9:58] Michal Koscielny
Description:
When I create a relationship between two tables in the EER Diagram, Workbench automatically creates the necessary index on the foreign key column - let's say it adds column A and creates an index on column A (named 'fk_..._idx'). But then I may decide I need another index on column A, some examples:

1) I add Primary index on column A
2) I add Unique index on column A
3) I add Primary, Unique or normal index that spans columns A, B, etc...

In any of the above scenarios I end up with two indexes on column A so in fact I don't need the original index that Workbench created when I was setting up the relationship and that index is not necessary any more. While MySQL will allow me to delete the index I cannot do it in Workbench modelling and I will get the following error:

"The index 'fk_..._idx' belongs to the Foreign Key 'fk_...'. You must delete the Foreign Key to delete the index".

Of course, I don't have to delete the FK to delete the index, I can delete the index alone directly in MySQL - then I can synchronize the model back with the database and the index is gone in Workbench, too. This is the possible workaround but it's inconvenient.

How to repeat:
Create two tables in the diagram, then create a relationship between them, for example 1:n. In the table that received the FK add a Primary or Unique index on the FK column. Then try to delete the original index named 'fk_..._idx' that Workbench created before. You will get an error message and will not be able to remove the index.

Suggested fix:
Workbench should be able to detect that there is another index on the FK column and allow to delete the original 'fk_..._idx' index. Having two indexes on the same column is redundant in most cases and only increases the amount of space needed to store data.

If that would be too much work an acceptable solution would be for Workbench to always allow deleting the indexes even in cases when they shouldn't be deleted and make the user responsible for it (if a necessary index is removed then there will be an error during synchronization). Optionally, a warning could be displayed before deleting the index. This would be much better than forbidding the deletion altogether.
[6 Aug 2013 11:47] MySQL Verification Team
Thank you for the bug report.
[14 Jan 2014 3:39] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.1.1 release, and here is the changelog entry:

Indexes that were auto-created for a Foreign Key can now be deleted in
case another suitable index was available.

Thank you for the bug report.