Bug #35262 Validating double identifiers doesn't seem to work...
Submitted: 13 Mar 2008 9:01 Modified: 8 Apr 2008 14:32
Reporter: Gert van de Venis Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.15 RC OS:Any
Assigned to:
Tags: double identifiers, validating, Validation
Triage: D3 (Medium)

[13 Mar 2008 9:01] Gert van de Venis
There seems to be a bug in the validating identifiers model validation...
I stumbled upon this when trying to sync my test database model with the server. It reported an error upon executing the generated sync script although there was no error in the script itself. Also the reported error wasn't clear AT ALL. However i traced the source of my problem. There was a double fk_name definition in my model. This was clearly a violation of the MySql "syntax", but it did not get reported as an error upon running a validation against the model. So to say it in short: The validation routine doesn't seem to report double fk_index definitions...

How to repeat:
- Create a database model with three tables table1, table2 and table3 and add a minimum of one column (with PK index set) to all three tables.

- Open the model options, override global options and change the foreign key name format to "fk_%table%_%column%" (without quotes). Close the dialog.

- Create a (non identifying) 1:n relationship between table1 and table2

- Ceate another (non identifying) 1:n relationship between table3 and table2.

Note that this creates two fk_keys with the same name! (at this point i already would 've expected an error message telling me i tried creating a double index name and providing a way to directly change the name, but i'll post this as a feature request later). 

- Now run a validation (only available in SE which is also a concern to me) and note that no errors are found! 

Suggested fix:
Change the fk_name manually.
- Edit one of the tables manually and select the "foreign key" tab,
- Change the fk_name to another (unique) name.
[13 Mar 2008 9:02] Gert van de Venis
This shows the example database wherein the problem is laid out

Attachment: Failing DuplicateIdentifiers.PNG (image/png, text), 96.68 KiB.

[13 Mar 2008 13:00] Miguel Solorzano
Thank you for the bug report.
[19 Mar 2008 8:42] Gert van de Venis
The problem still exists in the release candidate
[1 Apr 2008 16:15] Maksym Yehorov
[1 Apr 2008 18:43] Johannes Taxacher
validation checks and reports identical FK names now (checked in SVN Rev 2917)
[1 Apr 2008 18:43] Johannes Taxacher
validation checks and reports identical FK names now (checked in SVN Rev 2917)
[8 Apr 2008 14:32] MC Brown
A note has been added to the 5.0.17 changelog: 

When creating foreign key relationships that point to more than one table, the same foreign key identifier for the same table could be created. This would create invalid SQL code for creating the table.