Description:
In Workbench 8.0.15 Modelling tool on both Windows and MacOS if you change a relationship from non identifying to identifying the foreign key will be modified and become a primary key and a foreign key and turn the primary key of the table into a composite primary key.
This is not good modelling practice. The fewest number of columns should be used to identify a candidate key (primary key) of a table.
How to repeat:
-- TEST CASE for FK / PFK BUG for non identifying relationships
-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`ORDER`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`ORDER` (
`OrderID` INT NOT NULL,
`Username` VARCHAR(45) NOT NULL,
`Orderdate` DATE NOT NULL,
`CustomerID` CHAR(2) NOT NULL,
PRIMARY KEY (`OrderID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`Item`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Item` (
`ItemID` INT NOT NULL,
`Name` VARCHAR(12) NOT NULL,
`Price` DECIMAL(5,2) NOT NULL,
PRIMARY KEY (`ItemID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`OrderItem`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`OrderItem` (
`OrderItemID` INT NOT NULL,
`OrderID` VARCHAR(45) NOT NULL,
`ItemID` VARCHAR(45) NOT NULL,
`Quantity` INT NOT NULL,
PRIMARY KEY (`OrderItemID`),
INDEX `fk_OrderItem_ORDER_idx` (`OrderID` ASC) VISIBLE,
INDEX `fk_OrderItem_Item1_idx` (`ItemID` ASC) VISIBLE,
CONSTRAINT `fk_OrderItem_ORDER`
FOREIGN KEY (`OrderID`)
REFERENCES `mydb`.`ORDER` (`OrderID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_OrderItem_Item1`
FOREIGN KEY (`ItemID`)
REFERENCES `mydb`.`Item` (`ItemID`)
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;
-- Create this table structure in workbench modelling tool
i) Right click the relationship between OrderItem and Order tables
ii) select EDIT
iii) Click the 'foreign key' tab at the bottom of the window
iv) Check the identifying relationship saying that the orderitem table is dependent on their being an order
v) Close the relationship dialog (x top left)
The relationship will change from a dotted line - - - (non identifying) to identifying ______________ (solid line) ***HOWEVER*** the ORDERID foreign key is now a PFK (Primary Foreign Key) functioning as both a primary key and foreign key.
To confirm right click the OrderItem table and select 'Edit'
The table information will now identify OrderID as a Primary key along with OrderItemID. Editing the relationship should not change the foreign key and turn it into a primary key.
Suggested fix:
Do not allow the relationship type (identifying or non identifying) to change the table definition. A foreign key should not be changed to a primary foreign key because the relationship changes. It should stay as a foreign key (as was the behaviour in 6.3.9 of Workbench
Description: In Workbench 8.0.15 Modelling tool on both Windows and MacOS if you change a relationship from non identifying to identifying the foreign key will be modified and become a primary key and a foreign key and turn the primary key of the table into a composite primary key. This is not good modelling practice. The fewest number of columns should be used to identify a candidate key (primary key) of a table. How to repeat: -- TEST CASE for FK / PFK BUG for non identifying relationships -- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- -- ----------------------------------------------------- -- Schema mydb -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ; USE `mydb` ; -- ----------------------------------------------------- -- Table `mydb`.`ORDER` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`ORDER` ( `OrderID` INT NOT NULL, `Username` VARCHAR(45) NOT NULL, `Orderdate` DATE NOT NULL, `CustomerID` CHAR(2) NOT NULL, PRIMARY KEY (`OrderID`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`Item` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`Item` ( `ItemID` INT NOT NULL, `Name` VARCHAR(12) NOT NULL, `Price` DECIMAL(5,2) NOT NULL, PRIMARY KEY (`ItemID`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`OrderItem` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`OrderItem` ( `OrderItemID` INT NOT NULL, `OrderID` VARCHAR(45) NOT NULL, `ItemID` VARCHAR(45) NOT NULL, `Quantity` INT NOT NULL, PRIMARY KEY (`OrderItemID`), INDEX `fk_OrderItem_ORDER_idx` (`OrderID` ASC) VISIBLE, INDEX `fk_OrderItem_Item1_idx` (`ItemID` ASC) VISIBLE, CONSTRAINT `fk_OrderItem_ORDER` FOREIGN KEY (`OrderID`) REFERENCES `mydb`.`ORDER` (`OrderID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_OrderItem_Item1` FOREIGN KEY (`ItemID`) REFERENCES `mydb`.`Item` (`ItemID`) 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; -- Create this table structure in workbench modelling tool i) Right click the relationship between OrderItem and Order tables ii) select EDIT iii) Click the 'foreign key' tab at the bottom of the window iv) Check the identifying relationship saying that the orderitem table is dependent on their being an order v) Close the relationship dialog (x top left) The relationship will change from a dotted line - - - (non identifying) to identifying ______________ (solid line) ***HOWEVER*** the ORDERID foreign key is now a PFK (Primary Foreign Key) functioning as both a primary key and foreign key. To confirm right click the OrderItem table and select 'Edit' The table information will now identify OrderID as a Primary key along with OrderItemID. Editing the relationship should not change the foreign key and turn it into a primary key. Suggested fix: Do not allow the relationship type (identifying or non identifying) to change the table definition. A foreign key should not be changed to a primary foreign key because the relationship changes. It should stay as a foreign key (as was the behaviour in 6.3.9 of Workbench