1

I'm trying to create a Part that is a child of Sublot, which in turn is a child of Lot. Mysql Workbench automatically creates the foreign keys, when I insert the many-to-one relations graphically.

However I don't understand why it creates a 2nd foreign key (SubLot_Lot_idLot) in the bottom-most child.

I could try to find out if that is necessary by deleting this FK and then testing whether updates/deletes work the way I want them to.

But I would like to get feedback, as to whether this is the proper way to implement foreign keys in such a situation.

2nd (minimal) example

I used individual autoincrement keys the way that Mysql Workbench proposes. I understand that that is not necessarily the best way to make keys.

    -- MySQL Workbench Forward Engineering

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

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Lot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Lot` (
  `idLot` INT NOT NULL,
  `LotName` VARCHAR(45) NULL,
  PRIMARY KEY (`idLot`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`SubLot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`SubLot` (
  `idSubLot` INT NOT NULL,
  `SubLotName` VARCHAR(45) NULL,
  `Lot_idLot` INT NOT NULL,
  PRIMARY KEY (`idSubLot`, `Lot_idLot`),
  INDEX `fk_SubLot_Lot_idx` (`Lot_idLot` ASC),
  CONSTRAINT `fk_SubLot_Lot`
    FOREIGN KEY (`Lot_idLot`)
    REFERENCES `mydb`.`Lot` (`idLot`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Part`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Part` (
  `idPart` INT NOT NULL,
  `PartName` VARCHAR(45) NULL,
  `SubLot_idSubLot` INT NOT NULL,
  `SubLot_Lot_idLot` INT NOT NULL,
  PRIMARY KEY (`idPart`, `SubLot_idSubLot`, `SubLot_Lot_idLot`),
  INDEX `fk_Part_SubLot1_idx` (`SubLot_idSubLot` ASC, `SubLot_Lot_idLot` ASC),
  CONSTRAINT `fk_Part_SubLot1`
    FOREIGN KEY (`SubLot_idSubLot` , `SubLot_Lot_idLot`)
    REFERENCES `mydb`.`SubLot` (`idSubLot` , `Lot_idLot`)
    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;

I can avoid the 2nd FK in the bottom table, if I change the FK in the middle table to not be part of the PK. That, however changes the relationship to a non-identifying one. That is not my intent, since a SubLot cannot exist w/o a Lot, and a Part not without a Sublot.

So is the generation of the 2ndary FK in the bottom table just a consequence of the identifying relationship?

enter image description here

-- MySQL Workbench Forward Engineering

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

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`Lot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Lot` (
  `idLot` INT NOT NULL,
  `LotName` VARCHAR(45) NULL,
  PRIMARY KEY (`idLot`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`SubLot`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`SubLot` (
  `idSubLot` INT NOT NULL,
  `SubLotName` VARCHAR(45) NULL,
  `Lot_idLot` INT NOT NULL,
  PRIMARY KEY (`idSubLot`),
  INDEX `fk_SubLot_Lot_idx` (`Lot_idLot` ASC),
  CONSTRAINT `fk_SubLot_Lot`
    FOREIGN KEY (`Lot_idLot`)
    REFERENCES `mydb`.`Lot` (`idLot`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Part`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Part` (
  `idPart` INT NOT NULL,
  `PartName` VARCHAR(45) NULL,
  `SubLot_idSubLot` INT NOT NULL,
  PRIMARY KEY (`idPart`),
  INDEX `fk_Part_SubLot1_idx` (`SubLot_idSubLot` ASC),
  CONSTRAINT `fk_Part_SubLot1`
    FOREIGN KEY (`SubLot_idSubLot`)
    REFERENCES `mydb`.`SubLot` (`idSubLot`)
    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;
  • I found an answer by Bill Karwin : " by definition a foreign key in an identifying relationship is part of the child table's primary key." . So if we apply this rule twice, we have now the full PK of the middle table as FK in the third table. stackoverflow.com/questions/762937/… – peterd Aug 26 at 7:02
  • In a related post @Karwin also says : "Identifying relationships seem to be important only for the sake of entity-relationship diagramming, and this comes up in GUI data modeling tools." stackoverflow.com/questions/2814469/… That would give me comfort to move to non-identifying relationships here, even though I know that a Part can't live w/o a SubLot and so on. – peterd Aug 26 at 7:04
  • You can include your updates as edits to your question which makes it easier to read. p.s. welcome to the foru!m! :-) – Vérace Aug 26 at 8:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.