MySQL generated .sql gives Cannot add foreign key constraint ERROR

[Kubilay Doğukan]

This is the table that gives 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;

Here are Contract and Lawfirm Tables

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

[pritaeas]

It would be easier if you create the tables first, and afterwards add the constraints. Which application generated this script file?

[Kubilay Doğukan]

I used forward engineer of MySQL Workbench. I created relational diagrams first then it gives me this .sql

[pritaeas]

Am not very familiar with Workbench. Did you check "Create dump in single transaction" ? Or perhaps uncheck "create-options".

[Kubilay Doğukan]

Click Here Those are the only options I can choose.

[Kubilay Doğukan]

Problem Solved: Check constrain names if there is any constrain with the same name it gives this error.