mySql外键名称问题

Question

我在mysql工作台上设计了一个数据库。当我转到forward engineer时，我得到了（错误号:121)，因为我在多个表中使用了相同的外键，我意识到这是不允许的。我有相当多的连接表，主要是n：m关系。对于这些连接表中的大多数，我都使用复合主键（由2个外键组成）。我的问题是我必须重命名这些主键和外键是唯一的吗？谢谢你的帮助。

（在下面的代码中，我还没有重命名外键）

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

Answer 1

这对我来说很好。一个解决方案是只删除约束名称，它们将自动为您生成。

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