MySQL Bugs: #66167: Duplicate indexes while creating foreign keys in Workbench

Bug #66167	Duplicate indexes while creating foreign keys in Workbench
Submitted:	2 Aug 2012 17:24	Modified:	6 Nov 2012 19:49
Reporter:	Bob Dankert	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.41	OS:	Windows (W7)
Assigned to:		CPU Architecture:	Any
Tags:	duplicate index, foreign key, workbench

Description:
When creating a new foreign key in WorkBench, it automatically creates an index for the column(s) in the foreign key for the table you are altering.  This can be annoying when there is already an index with the same columns that Workbench is creating.  It would be nice if WorkBench would first check to see if a suitable index exists, and if not, then try to create the index.

How to repeat:
Create two tables as follows:

CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  `test1_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `test1_id` (`test1_id`)
);

Now try to create a foreign key on test2 (test1_id) to test1 (id) in Workbench and hit apply - It generates this SQL:

ALTER TABLE `TESTING`.`test2` 
  ADD CONSTRAINT `test`
  FOREIGN KEY (`test1_id` )
  REFERENCES `TESTING`.`test1` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `test` (`test1_id` ASC) ;

As you can see, it creates a new index on test2 indexing the field test2.test1_id, even though an identical index already exists when the table was first created.

Suggested fix:
Do not automatically create an index when adding a FK if a suitable one already exists.  It just results in duplicate indexes that easily go unnoticed.

Clearing up the synopsis

Thank you for the bug report.

Verified in:
Windows 7x64
WB 5.2.42 rev9752

ALTER TABLE `test`.`test2` 

  ADD CONSTRAINT `test`

  FOREIGN KEY (`test1_id` )

  REFERENCES `test`.`test1` (`id` )

  ON DELETE NO ACTION

  ON UPDATE NO ACTION

, ADD INDEX `test_idx` (`test1_id` ASC) ;

Duplicate of Bug #53277.

duplicate of Bug #53277