MySQL Bugs: #39367: duplicate constraint names generated

Bug #39367	duplicate constraint names generated
Submitted:	10 Sep 2008 10:22	Modified:	11 Sep 2008 13:44
Reporter:	Erwin Poeze	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.24	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	constraint, foreign key

Description:
If multiple tables have a foreign key with equal names, the sql output contains CONSTRAINT Symbol with that same name. 

It seems that for Symbol the name of the FOREIGN KEY is used. This results in Symbols that are not unique, which they should be.

How to repeat:
Make one referenced table and two referencing table with the same FOREIGN KEY.

CREATE TABEL one
idone ... (PK)

CREATE TABEL two
idtwo ... (PK)
idone ... (FK)
...
CONSTRAINT idone
 FOREIGN KEY

CREATE TABLE three
idthree .. (PK)
idone ... (FK)
...
CONSTRAINT idone
 FOREIGN KEY

Suggested fix:
Generate the name of the CONSTRAINT based on other information than only the name of the FOREIGN KEY

Hello Erwin,

could you describe how you are creating your constraints/FKs in workbench? I'm asking because if you're using the FK-tools to create relations WB creates uniqe identifiers for FKs (and therefore for the constraints) and if you're creating them via the table editor you have to be aware that FK names must be unique. (SE Version has a validation module that checks and informs the user about such situations)

Hello Johannes,

I did create the FK's via the table editor, but I was not aware that FK names have to be unique. You are right, if the FK names are unique, so are the CONSTRAINT names.

Problem solved, I suppose?

Thank you for a problem report. Indeed, duplicate constraint names generation is not prevented.

workbench does not prevent you from creating duplicate FK names, but it's not supposed to do so. SE version has a validation modules which alerts the user of a problem like this, but as long as you are using WBs Relation-Tools you should be fine because it generates FKnames based on templates set in options (which are by default id_table1_table2).