ERROR 1005: Can’t create table (errno: 121)

This error occur when i was working with MySQL and try to cascade the foreign key of more than two or more tables in child table with the primary key in parent table.

You can solve this problem by keeping the name of each foreign key different. I came to this conclusion after reading the comment MySQL forums http://forums.mysql.com/read.php?22,33999,87758#REPLY

 
Example-
parent table:

CREATE  TABLE `jpa`.`new_table` (
  `idnew_table` INT NOT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`idnew_table`) );

child table1:

CREATE  TABLE `jpa`.`new_table1` (
  `idnew_table1` INT NOT NULL ,
  PRIMARY KEY (`idnew_table1`) ,
  INDEX `idnew_table1` (`idnew_table1` ASC) ,
  CONSTRAINT `idnew_table1`
    FOREIGN KEY (`idnew_table1` )
    REFERENCES `jpa`.`new_table` (`idnew_table` )
    ON DELETE NO ACTION

ON UPDATE CASCADE);

child table2:

CREATE  TABLE `jpa`.`new_table2` (
  `idnew_table2` INT NOT NULL ,
  PRIMARY KEY (`idnew_table2`) ,
  INDEX `idnew_table2` (`idnew_table2` ASC) ,
  CONSTRAINT `idnew_table2`
    FOREIGN KEY (`idnew_table2` )
    REFERENCES `jpa`.`new_table` (`idnew_table` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE);

Here, the name of foreign key idnew_table2 and idnew_table1 are kept different than primary key idnew_table.

%d bloggers like this: