MySQL Bugs: #49987: "Skip Creation of FOREIGN KEYS" should exclude FOREIGN indexes

Bug #49987	"Skip Creation of FOREIGN KEYS" should exclude FOREIGN indexes
Submitted:	30 Dec 2009 8:10	Modified:	23 Mar 2010 17:52
Reporter:	Kelsey Prantis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.1.8, 5.2.11	OS:	Windows (XP)
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	forward engineering

Description:
When I select "Skip Creation of FOREIGN KEYS", it think should also not include the automatically generated FOREIGN indexes. 

I understand the argument of adding them to the schema in the first place since they are needed for InnoDB, but if I am exporting without the foreign key constraints, why should I be forced to include these indexes, which are often redundant with other indexes I've created?

It is not good to be forced to choose between being able to visualize the relationships in the model diagram and having the correct indexes in the resulting SQL. Just removing them from the output script is not an adequate solution in a multi-developer environment for large databases.

How to repeat:
Export a schema with Foreign keys and the associated indexes with the "Skip Creation of FOREIGN KEYS" checkbox selected. The output script will still contain the indexes.

Suggested fix:
Either don't include the FOREIGN  indexes when the "Skip Creation of FOREIGN KEYS" checkbox is checked, allow a way to specify whether or not to include them, or create a better way to model relations in just the diagram that aren't meant to be reflected in the SQL in any way.

With 5.2.11 on Windows XP "Skip Creation of FOREIGN KEYS" does NOT work at all in Export/Forward Engineer SQL CREATE Script..., for example. Not only indexes, but foreign keys are still created.

Triage: We need to discuss this.

Adding a comment to let you know that I am seeing the same (or similar) error on 5.2.11/Mac. Example output:

CREATE  TABLE IF NOT EXISTS `tempdb`.`comment` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `date` DATETIME NULL ,
  `message` TEXT NULL ,
  `account_id` INT NOT NULL ,
  `profile_id` INT NOT NULL ,
  `added` DATETIME NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_comment_friend1` (`account_id` ASC) ,
  INDEX `fk_comment_profile1` (`profile_id` ASC) ,
  CONSTRAINT `fk_comment_friend1`
    FOREIGN KEY (`account_id` )
    REFERENCES `myspace`.`account` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_comment_profile1`
    FOREIGN KEY (`profile_id` )
    REFERENCES `myspace`.`profile` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

indexes, specified as FOREIGN (thats what WB sets automatically created indexes for FK columns) are now skipped along with the keys when "Skip creation of FOREIGN KEYS" is checked.
fixed in repository

An entry has been added to the 5.2.17 changelog:

When a schema with Foreign keys and associated automatically generated foreign indexes was exported with the Skip Creation of FOREIGN KEYS checkbox selected, the generated script still contained the indexes.