mySql foreign key name issue

Question

Hi I have designed a database in mysql workbench. When I went to forward engineer I got (errno: 121) because I had foreign keys named the same in multiple tables which I realized is not allowed. I have quite a few junction tables as mostly n:m relationships. I am using composite primary keys (made up of 2 foreign keys) for most of these junction tables. My question is do I have to rename these primary keys to be unique along with the foreign keys? Appreciate some help please.

(I have not renamed the foreign keys yet in below code)

CREATE  TABLE IF NOT EXISTS `mydb`.`tblStudent` (
  `StudentID` INT(6) NOT NULL AUTO_INCREMENT ,
  `Student_Firstname` VARCHAR(20) NOT NULL ,
  `Student_Lastname` VARCHAR(20) NOT NULL ,
  `DOB` DATE NOT NULL ,
  `Student_Gender` ENUM('Male','Female') NOT NULL ,
  `Student_Address1` VARCHAR(40) NOT NULL ,
  `Student_Address2` VARCHAR(22) NOT NULL ,
  `Student_Address3` VARCHAR(14) NOT NULL ,
  `Student_Phonenum` INT(10) NULL ,
  `Student_Email` VARCHAR(60) NOT NULL ,
  `Student_Password` CHAR(128) NOT NULL ,
  `Enrollment_Date` DATE NOT NULL ,
  `Graduation_Date` DATE NULL ,
  `Student_Picture` VARCHAR(100) NOT NULL ,
  `PPSN` VARCHAR(9) NOT NULL ,
  PRIMARY KEY (`StudentID`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `mydb`.`tblIllness` (
  `IllnessID` CHAR(5) NOT NULL ,
  `Illness_Name` VARCHAR(30) NOT NULL ,
  PRIMARY KEY (`IllnessID`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `mydb`.`tblStudentIllness` (
  `IllnessID` CHAR(5) NOT NULL ,
  `StudentID` INT(6) NOT NULL ,
  `Doctor_Name` VARCHAR(30) NOT NULL ,
  `Doctor_Phonenum` INT(10) NOT NULL ,
  `Medication` VARCHAR(40) NOT NULL ,
  `Medical_Advice` VARCHAR(250) NOT NULL ,
  PRIMARY KEY (`IllnessID`, `StudentID`) ,
  INDEX `IllnessID_idx` (`IllnessID` ASC) ,
  INDEX `StudentID_idx` (`StudentID` ASC) ,
  CONSTRAINT `IllnessID`
    FOREIGN KEY (`IllnessID` )
    REFERENCES `mydb`.`tblIllness` (`IllnessID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `StudentID`
    FOREIGN KEY (`StudentID` )
    REFERENCES `mydb`.`tblStudent` (`StudentID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

Kermit Kermit · Accepted Answer · 2013-02-07T15:29:46

This compiles fine for me. One solution is to just remove the constraint names and they will be generated automatically for you.

...
CONSTRAINT 
    FOREIGN KEY (`IllnessID` )
    REFERENCES `tblIllness` (`IllnessID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
CONSTRAINT
    FOREIGN KEY (`StudentID` )
    REFERENCES `tblStudent` (`StudentID` )
    ON DELETE CASCADE
...

mySql foreign key name issue

1 Answers