MySQL Bugs: #38488: Duplicate relationships from DBDesigner models cause create script to fail

Bug #38488	Duplicate relationships from DBDesigner models cause create script to fail
Submitted:	31 Jul 2008 12:24	Modified:	5 Aug 2008 13:41
Reporter:	Simon Hodgson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0.23	OS:	Any
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any

Description:
In trying to import a model from DBDesigner 4, the original model has many relationships that have a name set, for exmaple 'is' or 'has'.

MySQL Workbench imports these relationships, the name from DBDesigner appears to be mapped to the caption and name properties in MySQL Workbench.

When exporting the MySQL Workbench model to a CREATE file, these names are used in the table defination when setting up the relationship (DBDesigner didn't use these names)

MySQL insists that the constraints have unqiue names, and hence the CREATE script for the new MySQL Workbench model fails, whereas previously the DBDesinger one worked OK.

Furthermore, whilst MySQL Workbench appears to allow the name and caption fields to be altered, when the CREATE script is generated the original name is still used. 

(I've taken a look at the Workbench generated XML file, and it appears to reference an 'Old Name' attribute, which I guess I can't edit, and is being used to build the constraint definition)

How to repeat:
Create a model with two relationships in DBDesigner, give them both the same name. See that the DB Designer create script executes OK.

Import the model into MySQL Workbench, export a new create script. See the new script fails when executed in MySQL.

Thank you for a problem report. Please, upload a DBDesigner model that demonstrates the behaviour described.

DBDesigner model that will fail

Attachment: example.xml (text/xml), 22.51 KiB.

Please see attached file

Verified just as described with your model. I've got this in the export script generated:

mysql> CREATE  TABLE IF NOT EXISTS `mydb`.`usr` (
    ->   `usr_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    ->   `grp_id` INT UNSIGNED NOT NULL ,
    ->   `company_id` INT UNSIGNED NOT NULL ,
    ->   `name` VARCHAR(45) NULL ,
    ->   PRIMARY KEY (`usr_id`) ,
    ->   CONSTRAINT `relation`
    ->     FOREIGN KEY (`company_id` )
    ->     REFERENCES `mydb`.`company` (`company_id` ),
    ->   CONSTRAINT `relation`
    ->     FOREIGN KEY (`grp_id` )
    ->     REFERENCES `mydb`.`grp` (`grp_id` ))
    -> PACK_KEYS = 0
    -> ROW_FORMAT = DEFAULT;
ERROR 1061 (42000): Duplicate key name 'relation'

FK constraint name in WB can't be empty. This requirement is implied by feature of synchronization with DBMS (it must be possible to map server side FK into model's one). That's why DBD4 importer was changed to generate unique name for every foreign key constraint. So now create statement for `usr` table will look the following way:

CREATE TABLE `usr` (
  `usr_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grp_id` int(10) unsigned NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`usr_id`),
  KEY `usr_FKIndex1` (`company_id`),
  KEY `usr_FKIndex2` (`grp_id`),
  CONSTRAINT `fk_{B6BD8B58-41D1-412C-8341-4E0304E815D6}` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`),
  CONSTRAINT `fk_{8F5CBA50-BED9-4207-AD54-68C0CA7315C4}` FOREIGN KEY (`grp_id`) REFERENCES `grp` (`grp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=0

And currently as a workaround you can change FK names manually. You should change the name of the foreign key itself through table editor instead of the caption of corresponding relation on diagram.

now WB generates uniqe constraint identifier when importing FKs from DBD4 documents.
this will go into 5.0.24

An entry was added to the 5.0.24 changelog:

When a DBDesigner 4 model that contained duplicate relationships was imported into Workbench, and then exported, the resultant script would fail when executed on MySQL server.