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:Microsoft 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)