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.
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.