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.