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