MySQL Bugs: #56907: Cannot create foreign key relationship using Table Editor

Bug #56907	Cannot create foreign key relationship using Table Editor
Submitted:	21 Sep 2010 19:47	Modified:	5 Dec 2010 17:28
Reporter:	Moua Xiong	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S1 (Critical)
Version:	5.2.27, 5.2.28	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	foreign key, unable to add to table

Description:
I've added a join table to a primary table.  However, when i try to go and add a foreign key relationship in the join table, the column with the check box to specify the field to use as the foreign key becomes disabled and I can't check any of the boxes to specify which field to use as the foreign key.  I can select a table as the reference table.  In addition, because I can't check a column, nothing appears in the referenced column as well.  I've tried refreshing my tables.

After running the script, I can go back in and then the fk will display correctly, but as soon as I uncheck the box, I can't add it anymore unless I run the script again.  So it looks like the gui editor can display it but I just can't add foreign keys with it.

I did notice that if add my foreign key via a script, it works fine but it just won't allow me to add the foreign key via the editor.  Any help would be appreciated.  Thank you.

How to repeat:
1.  create primary table with primary key.
2.  create join table.
3.  add foreign key to join table referencing primary table.
4.  cannot check box under column to specify field to use as foreign key.

Suggested fix:
I've had to run sql scripts to add my foreign key instead.

Could you please try version 5.2.28. Thanks in advance.

I upgraded to version 5.2.28 but that didn't help either.  I was able to go back into the table and see the FKs I added via scripting but then when I unchecked the checkbox under the column, I couldn't add it back in anymore.  At this point, I'm not sure what's causing that to happen.  Any additional help would be greatly appreciated as I'm really excited about the new workbench tool so I'd like it to work correctly.  Please let me know what else I could try.  Thanks.

Please let me know if you have any other suggestions.

What are the exact types of the columns?

The columns are a primary key in the main table and then an integer in the join table.

sounds like a type mismatch. if you create a table with a PK column typed as "INT", this column is created in server as INT(11) ... when you then create the second table and use "INT" again for the column you're about to link to the first table, WB refuses to wire "INT" and "INT(11)" ...
as a workaround, you can explicitly specify "INT(11)" as column type for the FK col in 2nd table - that should get WB to allow hooking those columns up.

if thats the problem you're facing, then this report is a duplicate of Bug #56024.

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".