MySQL Bugs: #44701: Cannot properly create more than one foreign key per table

Bug #44701	Cannot properly create more than one foreign key per table
Submitted:	6 May 2009 16:44	Modified:	10 Jun 2009 10:10
Reporter:	Evie White	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1.12	OS:	Any
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	CHECKED, foreign key, foreign key creation, foreign keys

Description:
When I go to the "Foreign Keys" tab to create multiple foreign keys on a table the foreign key's column and reference columns pick up the Column value from the last foreign key entry.

How to repeat:
Suppose that you have three tables defined as follows: 
TABLE_A : id, code
TABLE_B : id, code
TABLE_C : id, table_a_id, table_b_id

The table TABLE_C has two foreign key references:
1) The column "table_a_id" references TABLE_A.id
2) The column "table_b_id" references TABLE_B.id

To recreate the bug:
1) For TABLE_C, go to the Foreign Keys tab.
2) In the Foreign Key Name field, insert a name FK_table_a.
3) In the Reference Table field, select TABLE_A.
4) In the Column field, check table_a_id.
5) In the Reference Column field, pick id.

So far, so good. Let's create a second foreign key.

6) In the Foreign Key Name field, insert a name FK_table_b.
7) In the Reference Table field, select TABLE_B.

(Notice that table_a_id is already selected.)

8) In the Column field, UNcheck table_a_id and check table_b_id.
9) In the Reference Column field, pick id.

Looks good. But, wait...

10) Click on the first foreign key FK_table_a. You will see that it has both table_a_id and table_b_id checked.

Thank you for the problem report. Verified just as described with 5.1.12 on Windows XP. As a workaround one may use ERR Diagram editing instruments to create foreign keys (and then rename columns/foreign keys, if needed).

this has been fixed. fix will be included in 5.1.13

An entry was added to the 5.1.13 changelog:

Using the Foreign Keys tab of the Table Editor, it was not possible to correctly create multiple foreign key relationships.