MySQL Bugs: #69764: MySQL Workbench EER Diagram allows you to use duplicate foreign key names

Bug #69764	MySQL Workbench EER Diagram allows you to use duplicate foreign key names
Submitted:	17 Jul 2013 11:35	Modified:	17 Jul 2013 13:11
Reporter:	risman hidayat	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.47,6.0.2	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
Duplicate of http://bugs.mysql.com/bug.php?id=63642&thanks=3&notify=131

But on Mac OSX

Workbench version 5.2.47 Revision 10398

How to repeat:
Create 3 tables

child1
child2
parent

while making a relation between parent and children, give child1 and child2 the same foreign key name

Result: This doesn't yield an error, while it should

(After exporting the entire scheme to SQL , this will throw a very unclear exception when importing to a MySQL server, namely: ERROR 1022 (23000): Can't write; duplicate key in table 'errors_reports')

Suggested fix:
Show error message, or do what happened here http://bugs.mysql.com/bug.php?id=63642&thanks=3&notify=131

Hello Risman,

Thank you for the bug report. 
Verified as described on recent 5.2.47/6.0.2.

Thanks,
Umesh

EER daigram

Attachment: kkk.mwb (application/octet-stream, text), 11.76 KiB.

// SQL script generated from model

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `belsimpel` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `belsimpel` ;

-- -----------------------------------------------------
-- Table `belsimpel`.`exampletable`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `belsimpel`.`exampletable` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `should_be_enum` INT NULL ,
  `exampletablecol` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `belsimpel`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `belsimpel`.`table1` (
  `id` INT(10) NOT NULL ,
  `table1col` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `belsimpel`.`table2`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `belsimpel`.`table2` (
  `id` INT(10) NOT NULL ,
  `table2col` VARCHAR(45) NULL ,
  `table1_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `table1_fk_idx` (`table1_id` ASC) ,
  CONSTRAINT `table1_fk`
    FOREIGN KEY (`table1_id` )
    REFERENCES `belsimpel`.`table1` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `belsimpel`.`table3`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `belsimpel`.`table3` (
  `id` INT(10) NOT NULL ,
  `table3col` VARCHAR(45) NULL ,
  `table1_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `table1_fk_idx` (`table1_id` ASC) ,
  CONSTRAINT `table1_fk`
    FOREIGN KEY (`table1_id` )
    REFERENCES `belsimpel`.`table1` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Marking as Duplicate of Bug #63642

As per change log of 5.2.41 "MySQL Workbench would allow the creation of multiple foreign keys across multiple tables but with the same foreign key name. Synchronization would then emit a "Can't create table" error. (Bug #13548236, Bug #13500447, Bug #63642, Bug #62960)".. more details http://dev.mysql.com/doc/relnotes/workbench/en/wb-news-5-2-41.html