Bug #51990 Foreign Key problems

• Submitted: 12 Mar 2010 12:33
• Modified: 5 Jul 2013 13:22
• Reporter: Martin Pirringer
• Status: Closed
• Category: MySQL Workbench: Modeling
• Severity: S3 (Non-critical)
• Version: 5.2.16
• OS: Windows (7)
• CPU Architecture: Any
• Tags: foreign key, indexes

[12 Mar 2010 12:33] Martin Pirringer

Description:
A couple of foreign key problem

1.) on popup to select "referenced table" The current table selected is listed and can be selected producing a statement that will fail as it would create a circular reference to itself

2.) foreign keys on a primary key field will automatically genereate an index duplicating the primary key.

3.) it is possible to set up foreign keys on engines that do not support foreign keys (like MYISAM)

How to repeat:
create a new schema
add a table with a primary key 
add another table (INNODB)
click on foreign keys
click on referenced tables - you can select a table to reference itself will produce failing code 
it also creates a new index even though one exists

now create another table (MYISAM)
click on foreign keys ....

Suggested fix:
System should not allow you to make errors like that. adjust code to not give impossible options

[12 Mar 2010 23:07] MySQL Verification Team

Thank you for the bug report.

[15 Mar 2010 11:54] Johannes Taxacher

thanks for pointing those things out 

1.) OSS version of Workbench (as opposed to SE) does not feature validation of the model user is creating.
2.) we'll work on optimize that
3.) we decided not to restrain things like that because user may decide to switch engine (from myisam to innodb) and then he already has his keys setup. (also feeding MySQL Server FK definitions for myisam tables doesn't produce any  error - server just ignores those statements)

[5 Jul 2013 13:22] Alfredo Kojima

Item #2 has been fixed during the 5.2 series.