MySQL Bugs: #66489: Option "Generate separate CREATE INDEX statements" should do the same for FKs

Bug #66489	Option "Generate separate CREATE INDEX statements" should do the same for FKs
Submitted:	21 Aug 2012 20:38	Modified:	23 Sep 2012 6:18
Reporter:	Luke Stevens	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The option "Generate separate CREATE INDEX statements" handles foreign keys poorly. If the CREATE TABLE statement adds a FK without adding a suitable index at the same time, an index with the same name as the FK is implicitly generated. Then, if the subsequent CREATE INDEX statement tries to add an index of the same name, it will fail (others have noted, oddly, that it did not fail MySQL 5.0).

In Bug #60705, an attempt was made to fix this by changing the default index name to be different from the FK name, appending "_idx". I think this whole approach was based on a misunderstanding (see discussion there). If the index name is different, at least the generated script will succeed, but the table will have two indexes where only one was intended. If the user changes the index name back to match the FK (e.g., to conform to an existing schema design), the original problem of a failing script resurfaces.

Ultimately, the only solution is for this option to more intelligently take foreign keys into account.

How to repeat:
1. Create a model with two tables related by a foreign key.
2. Name the index for the foreign key as below.
3. Forward engineer a SQL CREATE script, turning on the option "Generate Separate CREATE INDEX Statements."
4. Run the generated SQL and observe the result.

Case 1. Index name different from FK (now the default). The resulting table has two indexes, "fk_foo" and "fk_foo_idx".
Case 2. Index name same as FK. The script fails because of duplicate index name.

Suggested fix:
Possible fixes:

1. Change the option to "Generate separate statements for index and foreign key creation." Instead of CREATE INDEX, do ALTER TABLE ADD CONSTRAINT... ADD INDEX. (This is handy for other reasons, such as being able to freely order the CREATE TABLE statements.)

2. Keep track of implicitly generated indexes, then instead of CREATE INDEX, do ALERT TABLE DROP INDEX... ADD INDEX... where the desired index is different from the default. Where it's not different, the statement could be suppressed altogether. (But then what's the point of the option for separate statements?)

What exact Workbench and server versions do you use? With 5.2.42 and recent 5.5.x I do NOT see two indexes after executing the script generated:

mysql> USE `mydb` ;
Database changed
mysql> 
mysql> -- -----------------------------------------------------
mysql> -- Table `mydb`.`table1`
mysql> -- -----------------------------------------------------
mysql> CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
    ->   `id` INT NOT NULL ,
    ->   PRIMARY KEY (`id`) )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.28 sec)

mysql> 
mysql> 
mysql> -- -----------------------------------------------------
mysql> -- Table `mydb`.`table2`
mysql> -- -----------------------------------------------------
mysql> CREATE  TABLE IF NOT EXISTS `mydb`.`table2` (
    ->   `id` INT NOT NULL ,
    ->   `table1_id` INT NOT NULL ,
    ->   PRIMARY KEY (`id`) ,
    ->   CONSTRAINT `fk_table2_table1`
    ->     FOREIGN KEY (`table1_id` )
    ->     REFERENCES `mydb`.`table1` (`id` )
    ->     ON DELETE NO ACTION
    ->     ON UPDATE NO ACTION)
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.40 sec)

mysql> 
mysql> CREATE INDEX `fk_table2_table1_idx` ON `mydb`.`table2` (`table1_id` ASC) ;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> 
mysql> SET SQL_MODE=@OLD_SQL_MODE;
Query OK, 0 rows affected (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
Query OK, 0 rows affected (0.00 sec)

mysql> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table table2\G
*************************** 1. row ***************************
       Table: table2
Create Table: CREATE TABLE `table2` (
  `id` int(11) NOT NULL,
  `table1_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_table2_table1_idx` (`table1_id`),
  CONSTRAINT `fk_table2_table1` FOREIGN KEY (`table1_id`) REFERENCES `table1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.05 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".