MySQL Workbench | ERROR: Error 1280: Incorrect index name

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 开发者_如何学PythonUNSIGNED 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;

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.

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.

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` )