Kdaydin Kdaydin - 1 year ago 107
MySQL Question

MySQL generated .sql Cannot add foreign key constraint

This is the table I get "Cannot add foreign key constraint" error.

-- -----------------------------------------------------
-- Table `mydb`.`Supervise1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Supervise1` (
`S1_Date` VARCHAR(45) NOT NULL,
`S1_Contracter` VARCHAR(45) NOT NULL,
`S1_Contractee` VARCHAR(45) NOT NULL,
`S1_ID` VARCHAR(45) NOT NULL,
PRIMARY KEY (`S1_Contracter`, `S1_Contractee`, `S1_Date`, `S1_ID`),
INDEX `Contracter_idx` (`S1_Contracter` ASC),
INDEX `Contractee_idx` (`S1_Contractee` ASC),
INDEX `S1_ID_idx` (`S1_ID` ASC),
CONSTRAINT `S1_Date`
FOREIGN KEY (`S1_Date`)
REFERENCES `mydb`.`Contract` (`Date`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `S1_Contracter`
FOREIGN KEY (`S1_Contracter`)
REFERENCES `mydb`.`Contract` (`Contracter`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `S1_Contractee`
FOREIGN KEY (`S1_Contractee`)
REFERENCES `mydb`.`Contract` (`Contractee`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `S1_ID`
FOREIGN KEY (`S1_ID`)
REFERENCES `mydb`.`Lawfirm` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


Contract and Lawfirm table

-- -----------------------------------------------------
-- Table `mydb`.`Contract`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Contract` (
`Date` VARCHAR(45) NOT NULL,
`Contracter` VARCHAR(45) NOT NULL,
`Contractee` VARCHAR(45) NOT NULL,
INDEX `Contracter_idx` (`Contracter` ASC),
INDEX `Contractee_idx` (`Contractee` ASC),
PRIMARY KEY (`Contracter`, `Contractee`, `Date`),
CONSTRAINT `Contracter`
FOREIGN KEY (`Contracter`)
REFERENCES `mydb`.`Agency` (`AgencyID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `Contractee`
FOREIGN KEY (`Contractee`)
REFERENCES `mydb`.`Agency` (`AgencyID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Lawfirm`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`Lawfirm` (
`ID` VARCHAR(45) NOT NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;


I looked through some answers for similar problem but mine columns are same type. I don't understand why it gives me error. I can provide more information if needed.

Answer Source

Solved: Check the sql if there is any constraint with the same name. Even it says foreign key error was about constraint names.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download