Bug #33658 Workbench Generates Primary Keys That Are Unnecessary and Can't Be Edited
Description:
When generating EER diagrams for Innodb tables with multiple relationships, MWB marks are the foreign keys for a table as part of the primary key. This is not necessary nor desired.
In addition, MWB will not let me edit these primary keys on the Indexes tab. When I choose the the index named "PRIMARY", I can see all of the Index Columns to the right that are checked. However, I cannot uncheck these columns that are from foreign keys.
Here's a sample of the SQL created from this problem. You can see that the PRIMARY KEY consists of several different columns. The only primary key that is necessary is "orders_id".
CREATE TABLE IF NOT EXISTS `coma`.`orders` (
`orders_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`order_number` CHAR(15) NOT NULL ,
`version` TINYINT UNSIGNED NOT NULL DEFAULT 0 ,
`order_service_type` CHAR(4) NOT NULL ,
`order_status_id` INT UNSIGNED NOT NULL ,
`nd_order_status_id` INT UNSIGNED NOT NULL ,
`company_id` INT UNSIGNED NOT NULL ,
`signaling_partner_id` SMALLINT UNSIGNED NOT NULL ,
`order_type_id` INT UNSIGNED NOT NULL ,
`modified` TIMESTAMP NOT NULL ,
`created` TIMESTAMP NULL ,
PRIMARY KEY (`orders_id`, `nd_order_status_id`, `company_id`, `order_status_id`, `signaling_partner_id`, `order_type_id`) ,
FOREIGN INDEX fk_orders_nd_order_status (`nd_order_status_id` ASC) ,
FOREIGN INDEX fk_orders_company (`company_id` ASC) ,
FOREIGN INDEX fk_orders_order_status (`order_status_id` ASC) ,
FOREIGN INDEX fk_orders_signaling_partner (`signaling_partner_id` ASC) ,
FOREIGN INDEX fk_orders_order_type (`order_type_id` ASC) ,
CONSTRAINT `fk_orders_nd_order_status`
FOREIGN KEY (`nd_order_status_id` )
REFERENCES `coma`.`nd_order_status` (`nd_order_status_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_company`
FOREIGN KEY (`company_id` )
REFERENCES `coma`.`company` (`company_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_order_status`
FOREIGN KEY (`order_status_id` )
REFERENCES `coma`.`order_status` (`order_status_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_signaling_partner`
FOREIGN KEY (`signaling_partner_id` )
REFERENCES `coma`.`signaling_partner` (`signaling_partner_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION,
CONSTRAINT `fk_orders_order_type`
FOREIGN KEY (`order_type_id` )
REFERENCES `coma`.`order_type` (`order_type_id` )
ON DELETE RESTRICT
ON UPDATE NO ACTION)
ENGINE = InnoDB;
How to repeat:
Create an EER with InnoDB tables that use multiple foreign keys.
Suggested fix:
Prevent MWB from assigning all foreign keys as part of the primary key.
Also, allow editing of any primary key column selections.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: When generating EER diagrams for Innodb tables with multiple relationships, MWB marks are the foreign keys for a table as part of the primary key. This is not necessary nor desired. In addition, MWB will not let me edit these primary keys on the Indexes tab. When I choose the the index named "PRIMARY", I can see all of the Index Columns to the right that are checked. However, I cannot uncheck these columns that are from foreign keys. Here's a sample of the SQL created from this problem. You can see that the PRIMARY KEY consists of several different columns. The only primary key that is necessary is "orders_id". CREATE TABLE IF NOT EXISTS `coma`.`orders` ( `orders_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `order_number` CHAR(15) NOT NULL , `version` TINYINT UNSIGNED NOT NULL DEFAULT 0 , `order_service_type` CHAR(4) NOT NULL , `order_status_id` INT UNSIGNED NOT NULL , `nd_order_status_id` INT UNSIGNED NOT NULL , `company_id` INT UNSIGNED NOT NULL , `signaling_partner_id` SMALLINT UNSIGNED NOT NULL , `order_type_id` INT UNSIGNED NOT NULL , `modified` TIMESTAMP NOT NULL , `created` TIMESTAMP NULL , PRIMARY KEY (`orders_id`, `nd_order_status_id`, `company_id`, `order_status_id`, `signaling_partner_id`, `order_type_id`) , FOREIGN INDEX fk_orders_nd_order_status (`nd_order_status_id` ASC) , FOREIGN INDEX fk_orders_company (`company_id` ASC) , FOREIGN INDEX fk_orders_order_status (`order_status_id` ASC) , FOREIGN INDEX fk_orders_signaling_partner (`signaling_partner_id` ASC) , FOREIGN INDEX fk_orders_order_type (`order_type_id` ASC) , CONSTRAINT `fk_orders_nd_order_status` FOREIGN KEY (`nd_order_status_id` ) REFERENCES `coma`.`nd_order_status` (`nd_order_status_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_orders_company` FOREIGN KEY (`company_id` ) REFERENCES `coma`.`company` (`company_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_orders_order_status` FOREIGN KEY (`order_status_id` ) REFERENCES `coma`.`order_status` (`order_status_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_orders_signaling_partner` FOREIGN KEY (`signaling_partner_id` ) REFERENCES `coma`.`signaling_partner` (`signaling_partner_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_orders_order_type` FOREIGN KEY (`order_type_id` ) REFERENCES `coma`.`order_type` (`order_type_id` ) ON DELETE RESTRICT ON UPDATE NO ACTION) ENGINE = InnoDB; How to repeat: Create an EER with InnoDB tables that use multiple foreign keys. Suggested fix: Prevent MWB from assigning all foreign keys as part of the primary key. Also, allow editing of any primary key column selections.