Description:
Problem using foreign keys, when runnig the script egnerated by MySQL WorkBench to create the scheme, we have the following error.
Executing SQL script in server ERROR: Error 1280: Incorrect index name 'fkPROD_PROVIDER' CREATE INDEX `fkPROD_PROVIDER` ON `mydb`.`PRODUCTS` (`PROVIDER` ASC) SQL script execution finished: statements: 10 succeeded, 1 failed
As you can see the name given to the foreign key index by MySQL WorkBench is the same as the name of the foreign key constraint.
How to repeat:
In this example I created a table “provider” and a table “products”. The table products have a foreign key referencing the provider id as you can see in the sript.
Go to Forwar engineer and check the following options in “Set options for Database to be created”
Generate DROP SCHEMA
Generate Separate CREATE INDEX STATEMENT
The SQL generated script is the following one
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`PROVIDERS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PROVIDERS` (
`idPROVIDERS` INT NOT NULL AUTO_INCREMENT ,
`PROVIDER_NAME` VARCHAR(45) NOT NULL ,
`PROVIDER_ADRESS` VARCHAR(255) NULL ,
PRIMARY KEY (`idPROVIDERS`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`PRODUCTS`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`PRODUCTS` (
`idPRODUCTS` INT NOT NULL AUTO_INCREMENT ,
`PRODUCT_NAME` VARCHAR(45) NULL ,
`PROVIDER` INT NOT NULL ,
PRIMARY KEY (`idPRODUCTS`) ,
CONSTRAINT `fkPROD_PROVIDER`
FOREIGN KEY (`PROVIDER` )
REFERENCES `mydb`.`PROVIDERS` (`idPROVIDERS` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fkPROD_PROVIDER` ON `mydb`.`PRODUCTS` (`PROVIDER` ASC) ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
When running the script, we have the following error.
Executing SQL script in server ERROR: Error 1280: Incorrect index name 'fkPROD_PROVIDER' CREATE INDEX `fkPROD_PROVIDER` ON `mydb`.`PRODUCTS` (`PROVIDER` ASC) SQL script execution finished: statements: 10 succeeded, 1 failed
Suggested fix:
As you can see the name given to the foreign key index by MySQL WorkBench is the same as the name of the foreign key constraint. To work around this problem you can go to the index tab in the model and rename the key or modify the script and give a different name to the index.
I suggest that MySQL WorkBench would add a prefix or a sufix to the index name.
Description: Problem using foreign keys, when runnig the script egnerated by MySQL WorkBench to create the scheme, we have the following error. Executing SQL script in server ERROR: Error 1280: Incorrect index name 'fkPROD_PROVIDER' CREATE INDEX `fkPROD_PROVIDER` ON `mydb`.`PRODUCTS` (`PROVIDER` ASC) SQL script execution finished: statements: 10 succeeded, 1 failed As you can see the name given to the foreign key index by MySQL WorkBench is the same as the name of the foreign key constraint. How to repeat: In this example I created a table “provider” and a table “products”. The table products have a foreign key referencing the provider id as you can see in the sript. Go to Forwar engineer and check the following options in “Set options for Database to be created” Generate DROP SCHEMA Generate Separate CREATE INDEX STATEMENT The SQL generated script is the following one SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; DROP SCHEMA IF EXISTS `mydb` ; CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `mydb` ; -- ----------------------------------------------------- -- Table `mydb`.`PROVIDERS` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`PROVIDERS` ( `idPROVIDERS` INT NOT NULL AUTO_INCREMENT , `PROVIDER_NAME` VARCHAR(45) NOT NULL , `PROVIDER_ADRESS` VARCHAR(255) NULL , PRIMARY KEY (`idPROVIDERS`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`PRODUCTS` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `mydb`.`PRODUCTS` ( `idPRODUCTS` INT NOT NULL AUTO_INCREMENT , `PRODUCT_NAME` VARCHAR(45) NULL , `PROVIDER` INT NOT NULL , PRIMARY KEY (`idPRODUCTS`) , CONSTRAINT `fkPROD_PROVIDER` FOREIGN KEY (`PROVIDER` ) REFERENCES `mydb`.`PROVIDERS` (`idPROVIDERS` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fkPROD_PROVIDER` ON `mydb`.`PRODUCTS` (`PROVIDER` ASC) ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; When running the script, we have the following error. Executing SQL script in server ERROR: Error 1280: Incorrect index name 'fkPROD_PROVIDER' CREATE INDEX `fkPROD_PROVIDER` ON `mydb`.`PRODUCTS` (`PROVIDER` ASC) SQL script execution finished: statements: 10 succeeded, 1 failed Suggested fix: As you can see the name given to the foreign key index by MySQL WorkBench is the same as the name of the foreign key constraint. To work around this problem you can go to the index tab in the model and rename the key or modify the script and give a different name to the index. I suggest that MySQL WorkBench would add a prefix or a sufix to the index name.