Mysql Workbench generates an additional FK in child of a child table referencing the Parent of its Parent

Question

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;