Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: IMPOSSIBLE TO CREATE MySQL 1:1 RELATION

    Hi all and thanks in advance,

    I use MySqlWorkbench on MySql 5.1. I'm using the 'EER schema design tool' and I need to create a 1:1 relationship between two table. With this tool I mangage to do that but when I do "forward engineer SQL create script" and run the script the relation become many to one!! I realize that because if I do "create EER model from existing DB" and I chose the DB I have created, the two table has a many to one relation!

    I use InnoDB engine.. I try many time to create a diagram with only two table connected with 1:1 relation and than create and run the script but that relation change automatically!

    Can you help me??

    thanks a lot

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can we see the "forward engineer SQL create script" output please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    2
    Yes sure, here there's the generated output:
    Code:
    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';
    
    DROP SCHEMA IF EXISTS `mydb` ;
    CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
    USE `mydb` ;
    
    -- -----------------------------------------------------
    -- Table `mydb`.`nation`
    -- -----------------------------------------------------
    DROP TABLE IF EXISTS `mydb`.`nation` ;
    
    CREATE  TABLE IF NOT EXISTS `mydb`.`nation` (
      `idnation` INT NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(45) NULL ,
      `capital city` VARCHAR(45) NULL ,
      PRIMARY KEY (`idnation`) )
    ENGINE = InnoDB
    AUTO_INCREMENT = 0;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`city`
    -- -----------------------------------------------------
    DROP TABLE IF EXISTS `mydb`.`city` ;
    
    CREATE  TABLE IF NOT EXISTS `mydb`.`city` (
      `idcity` INT NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(45) NULL ,
      `nation_idnation` INT NOT NULL ,
      PRIMARY KEY (`idcity`) ,
      INDEX `fk_city_nation` (`nation_idnation` ASC) ,
      CONSTRAINT `fk_city_nation`
        FOREIGN KEY (`nation_idnation` )
        REFERENCES `mydb`.`nation` (`idnation` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB
    AUTO_INCREMENT = 0;
    
    
    
    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i see only one relationship -- the one-to-many nation-to-city relationship

    were you trying to relate the capital city from nation back to city?

    you could, alternatively, have a column in city such as `is capital`
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it may well be a bug in Workbench
    it isn't a finished retail product, its a work in progress
    but then again I suppose you get what you pay for.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •