Bug #52245 mysql workbech change the type of relationship 1:1 to 1:N when I import a script
Submitted: 20 Mar 2010 23:30 Modified: 25 Mar 2010 12:46
Reporter: Erick Rivera Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:MySQL Workbench 5.1.16 OSS OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: 1:1, 1:N, change, relationship, workbench

[20 Mar 2010 23:30] Erick Rivera
Description:
If I design a model in MySQL Workbench with this structure

           1:N			1:1
customer ------- cust_account ------- account

MySQL Workbench generates the script below, wich is "similar" 
to that obtained with MySQL administrator backup option or
mysqldump --opt command.

But when I use the same script in the "import/Reverse
engineering..." option of MySQL Workbench, the connection
figure of the relationship 1:1 is changed to 1:N.

           1:N			1:N
customer ------- cust_account ------- account

I want to specify a relation 1:N in a explicit way, usign
a table, cust_account in this case, and the logic is right
but the connection figure is wrong.

thanks for read.

How to repeat:
-- -----------------------------------------------------
-- SCRIPT FROM 
-- MySQL Workbench 5.1.16 OSS Community edition
-- Revision 4210
-- -----------------------------------------------------

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';

CREATE SCHEMA IF NOT EXISTS `banca` DEFAULT CHARACTER SET latin1 ;
USE `banca`;

-- -----------------------------------------------------
-- Table `banca`.`account`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `banca`.`account` (
  `id_account` INT NOT NULL ,
  `settlement` DECIMAL(9,2) NOT NULL ,
  PRIMARY KEY (`id_account`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `banca`.`customer`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `banca`.`customer` (
  `id_customer` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_customer`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `banca`.`cust_account`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `banca`.`cust_account` (
  `id_account` INT NOT NULL ,
  `id_customer` INT NOT NULL ,
  INDEX `fk_cust_account_customer1` (`id_customer` ASC) ,
  PRIMARY KEY (`id_account`) ,
  INDEX `fk_cust_account_account1` (`id_account` ASC) ,
  CONSTRAINT `fk_cust_account_customer1`
    FOREIGN KEY (`id_customer` )
    REFERENCES `banca`.`customer` (`id_customer` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_cust_account_account1`
    FOREIGN KEY (`id_account` )
    REFERENCES `banca`.`account` (`id_account` )
    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;
[25 Mar 2010 12:46] Susanne Ebrecht
Many thanks for writing a bug report.

Definition:

1:N

This means N can be something between 1 and ∞

This means that 1:1 is part of 1:N

SQL has no difference between 1:1 and 1:N.

Neither MySQL server nor MySQL Workbench are artificial intelligent ... and so Workbench can't know that Foreign Key you created is 1:1.