MySQL Bugs: #35262: Validating double identifiers doesn't seem to work...

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:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.15 RC	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	double identifiers, validating, Validation

Description:
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.

This shows the example database wherein the problem is laid out

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

Thank you for the bug report.

The problem still exists in the release candidate

Fixed

validation checks and reports identical FK names now (checked in SVN Rev 2917)

validation checks and reports identical FK names now (checked in SVN Rev 2917)

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.