Bug #33658 Workbench Generates Primary Keys That Are Unnecessary and Can't Be Edited

Submitted: 3 Jan 2008 16:46 Modified: 8 Jan 2008 15:15
Reporter: Justin Noel Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench Severity:S2 (Serious)
Version:5.0.11 SE Beta Rev 2342 OS:Windows (XP 32 bit)
Assigned to: CPU Architecture:Any
Tags: Edit, key, primary, workbench

[3 Jan 2008 16:46] Justin Noel
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.
[4 Jan 2008 10:28] MySQL Verification Team
Thank you for the bug report.
[8 Jan 2008 15:15] Johannes Taxacher
- workbench adds referenced cols. to the PK when using the "Identifying" relations - If thats desired then using th "non-identifying" relations instead where the referenced cols. aren't added to the PK (only indexes for every referenced cols. are auto-created)

- if you want to remove a column from PK just double-click the "key"-icon on the column tab. The PRIMARY INDEX is adapted automatically.

- the created script that's not executed correctly is same as #33656 (thats why I marked this one  as duplicate)

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.