MySQL Bugs: #60705: identical names of CONSTRAINT and INDEX are created

Bug #60705	identical names of CONSTRAINT and INDEX are created
Submitted:	31 Mar 2011 8:29	Modified:	31 Jan 2012 6:44
Reporter:	Henrik Gemal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.33	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I see this when I try to forward SQL to my MySQL server:

CONSTRAINT `fk_misc_payments_currencies1`

CREATE INDEX `fk_misc_payments_currencies1` ON `booking_payments` (`currency_id` ASC) ;

This works fine on MySQL 5.1 but on 5.5 identical names are NOT allowed

How to repeat:
forward SQL

Suggested fix:
Workbench should generate unique names

Indeed, one gets error like this:

Executing SQL script in server

ERROR: Error 1061: Duplicate key name 'fk_table2_table1'

CREATE INDEX `fk_table2_table1` ON `mydb`.`table2` (`table1_idtable1` ASC) 
SQL script execution finished: statements: 7 succeeded, 1 failed

but only when "Generate separate CREATE INDEX statements" option is used.

This is fixed as of 5.2.40:

The "_idx" suffix is now added to foreign keys as they are created.

And Workbench checks for duplicate named foreign keys if a
document is loaded from a previous version of Workbench. And
if duplicates are found, then the user is given the choice of
renaming them.

Although this bug is closed, I think the change needs to be reconsidered.

Workbench will now prompt on opening an old model: "Index names identical to FK names were found in the model, which is not allowed for MySQL 5.5 and later." This seems to be the rationale for the changes, but it is not true.

The root cause of the problem originally reported is the attempt to re-create an index that was already implicitly created by defining the FK (I guess pre-5.5 versions somehow tolerated the duplicate). Giving the index a different name from the FK will still result in two indexes.

The proper solution should be to recognize that "Generate separate CREATE INDEX statements" is inherently entangled with foreign key creation. Instead of generating CREATE INDEX statements, I suppose instead you would have to generate ALTER TABLE ADD CONSTRAINT..., ADD INDEX..., and rename the option to something like "Generate separate statements for index and foreign key creation." (If you can be sure the CREATE INDEX precisely duplicates the implicitly generated index, you could just suppress that statement, but then you defeat the goal of the option for separate statements.)

The problem with the committed change is that it prevents users from doing something they may legitimately need to do. Most MySQL databases out there will have foreign key names identical to their corresponding index names. MySQL 5.5 has no problem with this and even still has this as default behavior. So how will we model systems where the schema is already defined with matching names?

The committed changes affect not only models, but also creating/altering real tables through the UI. Doing it in SQL still works, of course.

It's worth pointing out that Workbench's approach of tying an explicit index to each foreign key is the source of many problems, and I think many of us would rather see this behavior eliminated altogether. See Bug #53277.

Moving this to separate bugs, Bug #66488 and Bug #66489.