Bug #62960 Creating an identifying relationship does not create an index
Submitted: 27 Oct 2011 7:25 Modified: 24 Jul 2012 20:59
Reporter: Karsten Wutzke Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench: Modeling Severity:S1 (Critical)
Version:5.2.30, 5.2.35, 5.2.36, 5.2.37 OS:Windows
Assigned to: CPU Architecture:Any
Tags: identifying relationship, INDEX

[27 Oct 2011 7:25] Karsten Wutzke
When creating identifying relationships in the EER diagram window via the buttons to the right (one-to-many, one-to-one) WB does *not* create an index for the newly created foreign key.

How to repeat:
Take any table with a PK, then add a new one, then create an identifying relationship from the new one to the other table using the buttons to the left (alternatively press 3 or 4). Double-click the new table, view tab "Foreign Keys", you'll notice the new FK. Then view tab "Indexes" you will see that the respective index is missing.

Suggested fix:
create an index?
[27 Oct 2011 7:25] Karsten Wutzke
Sorry, I meant the buttons to the left.
[27 Oct 2011 16:38] Valeriy Kravchuk
What OS do you run Workbench on? I can not repeat this with 5.2.35 on Mac OS X.
[28 Oct 2011 17:16] Karsten Wutzke
I'm on Windows 7. Note this only applies to identifying relationships, non-id works fine.
[1 Nov 2011 13:28] Valeriy Kravchuk
I still can not repeat this with 5.2.35, even on Windows XP. Can you sent .mwb file that demonstrates the problem?
[2 Dec 2011 7:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[20 Dec 2011 17:35] Karsten Wutzke
demo mwb file

Attachment: bbstats.mwb (application/octet-stream, text), 15.74 KiB.

[20 Dec 2011 17:36] Karsten Wutzke
Try adding 1:1 and 1:n going from the right to left tables. Doesn't create indexes for me. Just tested with 5.2.35.
[22 Dec 2011 8:50] Valeriy Kravchuk
Verified just as described with your .mwb uploaded, with recent 5.2.36 on Windows XP.
[27 Dec 2011 15:32] Armando Lopez Valencia
Hello Karsten.
I just give a try with the new WB 5.2.37 and could not repeat this bug (I used your diagram and directions). Would you mind giving a try in your environment and let us know if you confirm the fix?
You can find the latest WB here: http://dev.mysql.com/downloads/workbench/
[27 Dec 2011 19:20] Karsten Wutzke
Doesn't work for me. The INDEX still isn't created.
[7 Feb 2012 16:22] Johannes Taxacher
Hello Karsten,

I have followed your instructions on how to recreate the issue. I'm ending up with a "PRIMARY" key that is added for the columns created in tables `B` and `Rosters` from your demo file, since you're using the "identifying relationship" tools. this PK already implies an index on that column, so having another explicit index created would be redundant.
Please verify if you see the same behavior (or if I am mistaking the issue).

thanks in advance
[7 Feb 2012 23:05] Karsten Wutzke
Yes, I get the same behavior. However, if what you describe is desired behavior then creating foreign keys to these tables via the table's "Foreign Keys" tab is the problem here.

Changing the bug report somewhat:

Use the attached MWB model, double-click table B, go to tab "Foreign Keys" and then highlight/focus the "Foreign Key Name" column (first row). By entering a name for the new foreign key an index is created. Choosing a target table for the new FK might not always show the id column to the right at first. If so activate the "Columns" tab. (Returning to the "Foreign Keys" tab now defaults to an id column for the newly created FK.) Activating the "Indexes" tab now reveals a second, redundant index on top of the PRIMARY index.

Whichever way you see it, both ways to create FKs are completely inconsistent to each other. So now the new way above will need to be fixed instead.

This actually is what I've had problems with all the time...

If you need further feedback just let me know.
[7 Feb 2012 23:13] Karsten Wutzke
I think I hadn't been quite sure about the two ways of creating new FKs giving different results myself, but I think going via the buttons 3 or 4 don't create extra indices (correct result) vs. the manual way via a table's "Foreign Keys" tab resulting in redundant indices (incorrect result).

In any case there should be tools to detect redundant indices in MWB model files when loading so that they're adjusted to the right way before tools like phpMyAdmin complain about redundant indices (which it does!).

I'm managing a few Python plugin and IIRC they've had problems with foreign keys not having an index with the same name. I'll have to re-check and see if this is still an issue. Stay tuned.
[23 Feb 2012 11:06] Karsten Wutzke
Any news on this issue?
[2 Apr 2012 12:51] Karsten Wutzke
What's the status on fixing this important usage inconsistency. I have the feeling no one is really looking at this bug closely enough or that it always involves different people. I'm really "disappointed" the way bugs are handled.
[24 Jul 2012 20:59] Philip Olson
This has been fixed as of the soon-to-be-released Workbench 5.2.41, and 
here's the changelog entry:

MySQL Workbench would allow the creation of multiple foreign keys
across multiple tables but with the same foreign key name.
Synchronization would then emit a "Can't create table" error.