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.
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?
-- 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;
Part
can't live w/o aSubLot
and so on. – peterd Aug 26 '19 at 7:04