What's with "id_" and "id" and "id" causes the Error 1005: Can't create table 'xxx.xxx' (errno: 121)

Question

I am new to MySQL and was creating a complex EER diagram. After creation, I "Forward Engineer..." the model and hit the dreaded ERROR: Error 1005: Can't create table 'xxx.xxx' (errno: 121). I created another simplified model of two tables with a 1:M relationship, but used "id_" for the primary and foreign key names. That worked.

i.e.
TABLE_A
  id_TABLE_A INT

TABLE_B
  id_TABLE_B INT
  id_TABLE_A INT

where, there is a one to many relationship between TABLE_A [1]--<[M] TABLE_B, and TABLE_B.id_TABLE_A is the foreign key

Looking at my complex EER diagram, I noticed I used "id" with no underscore for the primary and foreign key names. I inserted the underscore after "id" and Forward Engineer'ed the model and it worked with no errors. So, here are two simple example models, one with the "id_" and the other with "id". The "id_" DOES NOT cause the Error 1005 and the "id" causes the Error 1005. Anyone with an idea as to why this anomaly happens with MySQL?

=============================================================================== Good Model:

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';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

DROP TABLE IF EXISTS `mydb`.`TABLE_A` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`TABLE_A` (
  `id_TABLE_A` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id_TABLE_A`) ,
  UNIQUE INDEX `id_TABLE_A_UNIQUE` (`id_TABLE_A` ASC) )
ENGINE = InnoDB;


DROP TABLE IF EXISTS `mydb`.`TABLE_B` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`TABLE_B` (
  `id_TABLE_B` INT NOT NULL AUTO_INCREMENT ,
  `id_TABLE_A` INT NOT NULL ,
  PRIMARY KEY (`id_TABLE_B`) ,
  UNIQUE INDEX `id_TABLE_B_UNIQUE` (`id_TABLE_B` ASC) ,
  INDEX `id_TABLE_A` (`id_TABLE_A` ASC) ,
  CONSTRAINT `id_TABLE_A`
    FOREIGN KEY (`id_TABLE_A` )
    REFERENCES `mydb`.`TABLE_A` (`id_TABLE_A` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

RESULT:

Build is successful.

===============================================================================

Bad Model:

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';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;


DROP TABLE IF EXISTS `mydb`.`TABLE_A` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`TABLE_A` (
  `idTABLE_A` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`idTABLE_A`) ,
  UNIQUE INDEX `idTABLE_A_UNIQUE` (`idTABLE_A` ASC) )
ENGINE = InnoDB;


DROP TABLE IF EXISTS `mydb`.`TABLE_B` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`TABLE_B` (
  `idTABLE_B` INT NOT NULL AUTO_INCREMENT ,
  `idTABLE_A` INT NOT NULL ,
  PRIMARY KEY (`idTABLE_B`) ,
  UNIQUE INDEX `idTABLE_B_UNIQUE` (`idTABLE_B` ASC) ,
  INDEX `idTABLE_A` (`idTABLE_A` ASC) ,
  CONSTRAINT `idTABLE_A`
    FOREIGN KEY (`idTABLE_A` )
    REFERENCES `mydb`.`TABLE_A` (`idTABLE_A` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

RESULT:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'mydb.table_b' (errno: 121)

CREATE  TABLE IF NOT EXISTS `mydb`.`TABLE_B` (
  `idTABLE_B` INT NOT NULL AUTO_INCREMENT ,
  `idTABLE_A` INT NOT NULL ,
  PRIMARY KEY (`idTABLE_B`) ,
  UNIQUE INDEX `idTABLE_B_UNIQUE` (`idTABLE_B` ASC) ,
  INDEX `idTABLE_A` (`idTABLE_A` ASC) ,
  CONSTRAINT `idTABLE_A`
    FOREIGN KEY (`idTABLE_A` )
    REFERENCES `mydb`.`TABLE_A` (`idTABLE_A` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL script execution finished: statements: 8 succeeded, 1 failed

Answer 1

This would normally happen if your constraint name is conflicting with another name. See comments at http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html and http://forums.mysql.com/read.php?22,33999,76181#msg-76181

But here... A MySQL bug?