MySQL Workbench | ERROR: Error 1280: Incorrect index name

Question

I've got some problems using foreign keys in my database using InnoDB tables. I'm using MySQL Workbench for designing my ER models and have a Zend Server (OS X) for development with MySQL 5.1.54. Everything works fine without any error.

When deploying this database to my live server it fails. It is a Debian installation with MySQL 5.1.58-1~dotdeb.1-log. I can't understand why these two systems work so different.

ERROR: Error 1280: Incorrect index name 'fk_accounts_countries_idcountry'

DROP TABLE IF EXISTS `countries` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `countries` (
  `idcountry` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` CHAR(50) NOT NULL ,
  `prefix` CHAR(2) NULL DEFAULT NULL ,
  `tld` CHAR(4) NULL DEFAULT NULL ,
  PRIMARY KEY (`idcountry`) )
ENGINE = InnoDB
AUTO_INCREMENT = 270
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `accounts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `accounts` (
  `idaccount` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `countries_idcountry` SMALLINT UNSIGNED NOT NULL ,
  `fk_account` INT UNSIGNED NOT NULL ,
  `fk_country` INT UNSIGNED NOT NULL ,
  `username` CHAR(30) NOT NULL ,
  `password` CHAR(32) NOT NULL ,
  `mail` CHAR(50) NOT NULL ,
  `address` CHAR(50) NULL ,
  `city` CHAR(50) NOT NULL ,
  `company` CHAR(50) NULL ,
  `phone` CHAR(25) NULL ,
  `regdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `ratedate` TIMESTAMP NULL ,
  `lastlogin` TIMESTAMP NULL ,
  `activated` TINYINT(1)  NULL DEFAULT 0 ,
  `activation` CHAR(32) NULL ,
  PRIMARY KEY (`idaccount`) ,
  CONSTRAINT `fk_accounts_countries_idcountry`
    FOREIGN KEY (`countries_idcountry` )
    REFERENCES `countries` (`idcountry` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SHOW WARNINGS;
CREATE INDEX `fk_accounts_countries_idcountry` ON `accounts` (`countries_idcountry`ASC);

SHOW WARNINGS;

Answer 1

I know the topic is a bit old now, but I had this same problem today and found a quick and dirty solution, so I thought it would be nice to have it here for future reference.

---

Solution:

On MySQL Workbench, when you're forward engineering ( CTRL+G ), at the first dialog that shows up (Set Options for Database to be Created), simply uncheck the box labeled Generate Separate CREATE INDEX Statement.

Answer 2

On "Forward Engineer To Database" Uncheck the "Generate Seperate CREATE INDEX statements" checkbox. Checking this option will script the indexes twice, both in Create Table and after Creating Table.

Answer 3

Came across a similar case. Since the key is already created, you have to drop it, and add it again. For instance for my case, this is the SQL:

ALTER TABLE  `focuschamps`.`commit_later` DROP INDEX  `cid` , ADD UNIQUE  `cid` (  `cid` )