Bug #20160 duplicate foreign key names
Submitted: 31 May 2006 0:32 Modified: 31 May 2006 20:06
Reporter: Dominic Clifton Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Workbench Preview Severity:S3 (Non-critical)
Version:1.06 OS:Windows (win32)
Assigned to: CPU Architecture:Any

[31 May 2006 0:32] Dominic Clifton
Description:
when using one table's id as a foreign key in two other tables the sql create script will attempt to create two foreign keys with the same name.  On InnoDB this is a problem and results in a error:

060530  0:24:19 Error in foreign key constraint creation for table `test/releaseinfo_ssinfo`.
A foreign key constraint of name `test/FKReleaseInfo`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

How to repeat:
create three tables, one with an autoid field called "id", leave the other tables empty
use the 1:1 relationship tool to put put the id field into the other tables so that you get "fkid" fields on the other tables.
export an sql create script
create a mysql db that uses the innodb storage engine
run the create script

here's the relevent lines from the sql create script that had to be changed:

Original:

create table ...
  CONSTRAINT `FKTableA` FOREIGN KEY `FKTableA` (`FKTableAid`)
...
create table ...
  CONSTRAINT `FKTableA` FOREIGN KEY `FKTableA` (`FKTableAid`)
...

Fixed:

create table ...
  CONSTRAINT `FKTableA_TableB` FOREIGN KEY `FKTableA` (`FKTableAid`)
...

create table ...
  CONSTRAINT `FKTableA_TableC` FOREIGN KEY `FKTableA` (`FKTableAid`)
...

Suggested fix:
append to the name that's used for the constraint one of these:

1) a random number
2) an incrementing number
3) the table names that the contraint is being created on
4) the field name that is being used from the source table
5) a combination of 3 and 4 and 5

using the example above:

FKTableA_TableB_TableAid_1
FKTableA_TableB_TableAid_2

would be good where table b has two constraints for two fields using the same field 'id' from table a
[31 May 2006 20:06] Jorge del Conde
Hi!

Thanks for your excellent bug report.  I was able to reproduce this under WinXP64.