Creating foreign keys on already indexed columns using MySQL Workbench

Question

I create a database model with Workbench and create the following table:

CREATE TABLE IF NOT EXISTS `Database`.`table1` ( `idtable1` INT NOT NULL , `uniquecolumn` INT NOT NULL , PRIMARY KEY (`idtable1`) , UNIQUE INDEX `UniqueIndex` (`uniquecolumn` ASC) ) ENGINE = InnoDB 

It has a primary key and a unique key in my second column.

When I create foreign key constraints for them, Workbench automatically adds two indexes:

 CREATE TABLE IF NOT EXISTS `Database`.`table1` ( `idtable1` INT NOT NULL , `uniquecolumn` INT NOT NULL , PRIMARY KEY (`idtable1`) , UNIQUE INDEX `UniqueIndex` (`uniquecolumn` ASC) , INDEX `FKOne` (`idtable1` ASC) , //here INDEX `FKTwo` (`uniquecolumn` ASC) , //(I don't want this!) CONSTRAINT `FKOne` FOREIGN KEY (`idtable1` ) REFERENCES `Database`.`table2` (`idtable2` ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FKTwo` FOREIGN KEY (`uniquecolumn` ) REFERENCES `Database`.`table2` (`idtable2` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB 

(The above script method with direct input support after adding foreign keys to my model)

Now I have four indexes.

This is what the MySQL reference manual says:

The reference table should have an index where the foreign key columns are listed as the first columns in the same order. Such an index is automatically created in the lookup table if it does not exist.

Therefore, I understand that there is no need to create FKOne and FKTwo , since in the same order there is already a primary key and a unique index in the same columns. However, MySQL Workbench does not allow me to delete the FKOne and FKTwo . And I think I can do it:

 CREATE TABLE IF NOT EXISTS `Database`.`table1` ( `idtable1` INT NOT NULL , `uniquecolumn` INT NOT NULL , PRIMARY KEY (`idtable1`) , UNIQUE INDEX `UniqueIndex` (`uniquecolumn` ASC) , CONSTRAINT `FKOne` FOREIGN KEY (`idtable1` ) REFERENCES `Database`.`table2` (`idtable2` ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FKTwo` FOREIGN KEY (`uniquecolumn` ) REFERENCES `Database`.`table2` (`idtable2` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB 

I'm right? Will this code work? Is there a way to do this using Workbench? (In addition to removing these two lines at the last moment before forward engineering).

Or maybe MySQL is smart enough to refrain from creating completely redundant indexes, and I don't need to worry about that ...?

Answer 1

(I assume this is when defining the model.)

See Error 53277 , where I mention the following obscure workaround:

You start with the foreign key and its corresponding generated index, which you want to get rid of. Make sure that the key (at least temporarily) is in the same column as the unique one. On the Indexes tab, change the type to UNIQUE. Then go to the Columns tab, where UQ is now checked, and clear the check box. Unwanted index is excluded!