Description:
Thanks for a great program! Love to use it for EER!
Using InnoDB.
When creating a multi-field FOREIGN KEY, the field order is determined by the order of field selection, but cannot be changed after this. However, the field order in the accompanying automatically created index can be altered. It may make sense to alter the index field order to allow the index to better support normal data access. However, now the FOREIGN KEY field order and the backing index field order are no longer the same. As a consequence, InnoDB attempts to automatically create an index with the correct order. The name collides with the one already created by mysql-workbench. This causes the create SQL to fail.
This can be corrected by allowing the reordering of fields in a FOREIGN KEY (and maybe not in the corresponding index directly).
It would also be desirable if the FOREIGN KEY part of mysql-workbench could detect already existing indexes with the correct leading field order to support a FOREIGN KEY constraint, or even automatically order fields in such a constraint to take advantage of existing indexes.
I marked this non-ciritical, because there is a work-around and most foreign keys are single field. When it happens, it's very hard to from error messages etc figure out what's going on.
Thanks!
How to repeat:
1. Create table with foreign key. Note the field order in the key, which is determined by the order in which I selected the component fields. There is no way to change the order, once the constraint has been created.
-- -----------------------------------------------------
-- Table `ydm`.`Ycase`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ydm`.`Ycase` (
`idYcase` INT NOT NULL ,
`idPrimaryTask` INT NULL ,
`idUni` INT NOT NULL ,
PRIMARY KEY (`idUni`, `idYcase`) ,
INDEX `fk_Ycase_Task` (`idPrimaryTask` ASC, `idUni` ASC) ,
CONSTRAINT `fk_Ycase_Task`
FOREIGN KEY (`idPrimaryTask` , `idUni` )
REFERENCES `ydm`.`Task` (`idTask` , `idUni` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
2. Now, go into the index and reverse the order of the fields. In my case, idUni is the client id and idPrimaryTask relevant within the client, so it makes much more sense to have that order in indices for other reasons. Note that the index now leads with idUni whereas the constraint does not.
-- -----------------------------------------------------
-- Table `ydm`.`Ycase`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ydm`.`Ycase` (
`idYcase` INT NOT NULL ,
`idPrimaryTask` INT NULL ,
`idUni` INT NOT NULL ,
PRIMARY KEY (`idUni`, `idYcase`) ,
INDEX `fk_Ycase_Task` (`idUni` ASC, `idPrimaryTask` ASC) ,
CONSTRAINT `fk_Ycase_Task`
FOREIGN KEY (`idPrimaryTask` , `idUni` )
REFERENCES `ydm`.`Task` (`idTask` , `idUni` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
When applying this to the database, I get an error, because InnoDB tries to automatically create an index fk_Ycase_Task (idPrimaryTask, idUni) to back the constraint, but an index with that name already exists with the other field order:
ERROR: Error 1061: Duplicate key name 'fk_Ycase_Task'
3. I can fix this by deleting the constraint in mysql-workbench and recreating it, clicking idUni first, then idPrimaryTask. This way, the constraint and the automatically defined index are in the desired order.
Suggested fix:
Ideally, allow re-ordering of fields in a Foreign Key definition.
Since InnoDB automatically creates the index, it may be worthwhile to allow suppression of index definition in mysql-workbench. There are also cases where an index is automatically defined (no way to get rid of it without removing the foreign key), even thought there are other indexes that support the constraint. For instance, PRIMARY KEY (a, b, c) and FOREIGN KEY (a, b).
Thanks!
Fred
Description: Thanks for a great program! Love to use it for EER! Using InnoDB. When creating a multi-field FOREIGN KEY, the field order is determined by the order of field selection, but cannot be changed after this. However, the field order in the accompanying automatically created index can be altered. It may make sense to alter the index field order to allow the index to better support normal data access. However, now the FOREIGN KEY field order and the backing index field order are no longer the same. As a consequence, InnoDB attempts to automatically create an index with the correct order. The name collides with the one already created by mysql-workbench. This causes the create SQL to fail. This can be corrected by allowing the reordering of fields in a FOREIGN KEY (and maybe not in the corresponding index directly). It would also be desirable if the FOREIGN KEY part of mysql-workbench could detect already existing indexes with the correct leading field order to support a FOREIGN KEY constraint, or even automatically order fields in such a constraint to take advantage of existing indexes. I marked this non-ciritical, because there is a work-around and most foreign keys are single field. When it happens, it's very hard to from error messages etc figure out what's going on. Thanks! How to repeat: 1. Create table with foreign key. Note the field order in the key, which is determined by the order in which I selected the component fields. There is no way to change the order, once the constraint has been created. -- ----------------------------------------------------- -- Table `ydm`.`Ycase` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `ydm`.`Ycase` ( `idYcase` INT NOT NULL , `idPrimaryTask` INT NULL , `idUni` INT NOT NULL , PRIMARY KEY (`idUni`, `idYcase`) , INDEX `fk_Ycase_Task` (`idPrimaryTask` ASC, `idUni` ASC) , CONSTRAINT `fk_Ycase_Task` FOREIGN KEY (`idPrimaryTask` , `idUni` ) REFERENCES `ydm`.`Task` (`idTask` , `idUni` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; 2. Now, go into the index and reverse the order of the fields. In my case, idUni is the client id and idPrimaryTask relevant within the client, so it makes much more sense to have that order in indices for other reasons. Note that the index now leads with idUni whereas the constraint does not. -- ----------------------------------------------------- -- Table `ydm`.`Ycase` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `ydm`.`Ycase` ( `idYcase` INT NOT NULL , `idPrimaryTask` INT NULL , `idUni` INT NOT NULL , PRIMARY KEY (`idUni`, `idYcase`) , INDEX `fk_Ycase_Task` (`idUni` ASC, `idPrimaryTask` ASC) , CONSTRAINT `fk_Ycase_Task` FOREIGN KEY (`idPrimaryTask` , `idUni` ) REFERENCES `ydm`.`Task` (`idTask` , `idUni` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; When applying this to the database, I get an error, because InnoDB tries to automatically create an index fk_Ycase_Task (idPrimaryTask, idUni) to back the constraint, but an index with that name already exists with the other field order: ERROR: Error 1061: Duplicate key name 'fk_Ycase_Task' 3. I can fix this by deleting the constraint in mysql-workbench and recreating it, clicking idUni first, then idPrimaryTask. This way, the constraint and the automatically defined index are in the desired order. Suggested fix: Ideally, allow re-ordering of fields in a Foreign Key definition. Since InnoDB automatically creates the index, it may be worthwhile to allow suppression of index definition in mysql-workbench. There are also cases where an index is automatically defined (no way to get rid of it without removing the foreign key), even thought there are other indexes that support the constraint. For instance, PRIMARY KEY (a, b, c) and FOREIGN KEY (a, b). Thanks! Fred