MySQL Workbench Datamodell Error 1215: Cannot add foreign key constraint

Question

I have the following Error. I am using InnoDB everywhere. Can' find the mistake because I actually didnt change anything. I just made the tables and clicked them together in the Diagramm. Some weeks ago it actually did work this way. Then I tried to change the modell and now nothing is working.

Executing SQL script in server ERROR: Error 1215: Cannot add foreign key constraint SQL Code:

        -- -----------------------------------------------------
        -- Table `ProDevCBR`.`Thecase`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `ProDevCBR`.`Thecase` (
          `idThecase` INT NOT NULL AUTO_INCREMENT,
          `Problemtext` VARCHAR(45) NULL,
          `Temperature` DOUBLE NULL,
          `Pressure` DOUBLE NULL,
          `Figureofmerit` DOUBLE NULL,
          `Createtime` TIMESTAMP NULL,
          `Pumpelement_idPumpelement` INT NOT NULL,
          `Pumpmodel_idPumpmodel` INT NOT NULL,
          `Kindofdamage_idKindofdamage` INT NOT NULL,
          `User_idUser` INT NOT NULL,
          PRIMARY KEY (`idThecase`, `Pumpelement_idPumpelement`, `Pumpmodel_idPumpmodel`, `User_idUser`, `Kindofdamage_idKindofdamage`),
          INDEX `fk_Thecase_Pumpelement_idx` (`Pumpelement_idPumpelement` ASC),
          INDEX `fk_Thecase_Pumpmodel_idx` (`Pumpmodel_idPumpmodel` ASC),
          INDEX `fk_Thecase_Kindofdamage_idx` (`Kindofdamage_idKindofdamage` ASC),
          INDEX `fk_Thecase_User_idx` (`User_idUser` ASC),
          CONSTRAINT `fk_Thecase_Pumpelemen`
            FOREIGN KEY (`Pumpelement_idPumpelement`)
            REFERENCES `ProDevCBR`.`Pumpelement` (`idPumpelement`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_Thecase_Pumpmodel`
            FOREIGN KEY (`Pumpmodel_idPumpmodel`)
            REFERENCES `ProDevCBR`.`Pumpmodel` (`idPumpmodel`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_Thecase_Kindofdamage`
            FOREIGN KEY (`Kindofdamage_idKindofdamage`)
            REFERENCES `ProDevCBR`.`Kindofdamage` (`idKindofdamage`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_Thecase_User`
            FOREIGN KEY (`User_idUser`)
            REFERENCES `ProDevCBR`.`User` (`idUser`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

SQL script execution finished: statements: 9 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch
Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
        -- -----------------------------------------------------
        -- Table `ProDevCBR`.`Thecase`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `ProDevCBR`.`Thecase` (
          `idThecase` INT NOT NULL AUTO_INCREMENT,
          `Problemtext` VARCHAR(45) NULL,
          `Temperature` DOUBLE NULL,
          `Pressure` DOUBLE NULL,
          `Figureofmerit` DOUBLE NULL,
          `Createtime` TIMESTAMP NULL,
          `Pumpelement_idPumpelement` INT NOT NULL,
          `Pumpmodel_idPumpmodel` INT NOT NULL,
          `Kindofdamage_idKindofdamage` INT NOT NULL,
          `User_idUser` INT NOT NULL,
          PRIMARY KEY (`idThecase`, `Pumpelement_idPumpelement`, `Pumpmodel_idPumpmodel`, `User_idUser`, `Kindofdamage_idKindofdamage`),
          INDEX `fk_Thecase_Pumpelement_idx` (`Pumpelement_idPumpelement` ASC),
          INDEX `fk_Thecase_Pumpmodel_idx` (`Pumpmodel_idPumpmodel` ASC),
          INDEX `fk_Thecase_Kindofdamage_idx` (`Kindofdamage_idKindofdamage` ASC),
          INDEX `fk_Thecase_User_idx` (`User_idUser` ASC),
          CONSTRAINT `fk_Thecase_Pumpelemen`
            FOREIGN KEY (`Pumpelement_idPumpelement`)
            REFERENCES `ProDevCBR`.`Pumpelement` (`idPumpelement`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_Thecase_Pumpmodel`
            FOREIGN KEY (`Pumpmodel_idPumpmodel`)
            REFERENCES `ProDevCBR`.`Pumpmodel` (`idPumpmodel`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_Thecase_Kindofdamage`
            FOREIGN KEY (`Kindofdamage_idKindofdamage`)
            REFERENCES `ProDevCBR`.`Kindofdamage` (`idKindofdamage`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_Thecase_User`
            FOREIGN KEY (`User_idUser`)
            REFERENCES `ProDevCBR`.`User` (`idUser`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

SQL script execution finished: statements: 9 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

----------------------------------------------------------------------------------------------------


SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema ProDevCBR
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `ProDevCBR` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `ProDevCBR` ;

-- -----------------------------------------------------
-- Table `ProDevCBR`.`Pumpelement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ProDevCBR`.`Pumpelement` (
  `idPumpelement` INT NOT NULL AUTO_INCREMENT,
  `namePumpelement` VARCHAR(45) NULL,
  PRIMARY KEY (`idPumpelement`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ProDevCBR`.`Pumpmodel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ProDevCBR`.`Pumpmodel` (
  `idPumpmodel` INT NOT NULL AUTO_INCREMENT,
  `namePumpmodel` VARCHAR(45) NULL,
  PRIMARY KEY (`idPumpmodel`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ProDevCBR`.`Kindofdamage`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ProDevCBR`.`Kindofdamage` (
  `idKindofdamage` INT NOT NULL AUTO_INCREMENT,
  `nameKindofdamage` VARCHAR(45) NULL,
  PRIMARY KEY (`idKindofdamage`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ProDevCBR`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ProDevCBR`.`User` (
  `idUser` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NULL,
  `Email` VARCHAR(45) NULL,
  `Password` VARCHAR(45) NULL,
  PRIMARY KEY (`idUser`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ProDevCBR`.`Thecase`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ProDevCBR`.`Thecase` (
  `idThecase` INT NOT NULL AUTO_INCREMENT,
  `Problemtext` VARCHAR(45) NULL,
  `Temperature` DOUBLE NULL,
  `Pressure` DOUBLE NULL,
  `Figureofmerit` DOUBLE NULL,
  `Createtime` TIMESTAMP NULL,
  `Pumpelement_idPumpelement` INT NOT NULL,
  `Pumpmodel_idPumpmodel` INT NOT NULL,
  `Kindofdamage_idKindofdamage` INT NOT NULL,
  `User_idUser` INT NOT NULL,
  PRIMARY KEY (`idThecase`, `Pumpelement_idPumpelement`, `Pumpmodel_idPumpmodel`, `User_idUser`, `Kindofdamage_idKindofdamage`),
  INDEX `fk_Thecase_Pumpelement_idx` (`Pumpelement_idPumpelement` ASC),
  INDEX `fk_Thecase_Pumpmodel_idx` (`Pumpmodel_idPumpmodel` ASC),
  INDEX `fk_Thecase_Kindofdamage_idx` (`Kindofdamage_idKindofdamage` ASC),
  INDEX `fk_Thecase_User_idx` (`User_idUser` ASC),
  CONSTRAINT `fk_Thecase_Pumpelemen`
    FOREIGN KEY (`Pumpelement_idPumpelement`)
    REFERENCES `ProDevCBR`.`Pumpelement` (`idPumpelement`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Thecase_Pumpmodel`
    FOREIGN KEY (`Pumpmodel_idPumpmodel`)
    REFERENCES `ProDevCBR`.`Pumpmodel` (`idPumpmodel`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Thecase_Kindofdamage`
    FOREIGN KEY (`Kindofdamage_idKindofdamage`)
    REFERENCES `ProDevCBR`.`Kindofdamage` (`idKindofdamage`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Thecase_User`
    FOREIGN KEY (`User_idUser`)
    REFERENCES `ProDevCBR`.`User` (`idUser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ProDevCBR`.`Solution`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ProDevCBR`.`Solution` (
  `idSolution` INT NOT NULL AUTO_INCREMENT,
  `Solutiontext` VARCHAR(45) NULL,
  `Createtime` TIMESTAMP NULL,
  `Thecase_idThecase` INT NOT NULL,
  `Thecase_Pumpelement_idPumpelement` INT NOT NULL,
  `Thecase_Pumpmodel_idPumpmodel` INT NOT NULL,
  `Thecase_User_idUser` INT NOT NULL,
  PRIMARY KEY (`idSolution`),
  INDEX `fk_Solution_Thecase1_idx` (`Thecase_idThecase` ASC, `Thecase_Pumpelement_idPumpelement` ASC, `Thecase_Pumpmodel_idPumpmodel` ASC, `Thecase_User_idUser` ASC),
  CONSTRAINT `fk_Solution_Thecase1`
    FOREIGN KEY (`Thecase_idThecase` , `Thecase_Pumpelement_idPumpelement` , `Thecase_Pumpmodel_idPumpmodel` , `Thecase_User_idUser`)
    REFERENCES `ProDevCBR`.`Thecase` (`idThecase` , `Pumpelement_idPumpelement` , `Pumpmodel_idPumpmodel` , `User_idUser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ProDevCBR`.`Keyword`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ProDevCBR`.`Keyword` (
  `idKeyword` INT NOT NULL AUTO_INCREMENT,
  `nameKeyword` VARCHAR(45) NULL,
  PRIMARY KEY (`idKeyword`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ProDevCBR`.`Keyword_has_Thecase`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ProDevCBR`.`Keyword_has_Thecase` (
  `Keyword_idKeyword` INT NOT NULL,
  `Thecase_idThecase` INT NOT NULL,
  `Thecase_Pumpelement_idPumpelement` INT NOT NULL,
  `Thecase_Pumpmodel_idPumpmodel` INT NOT NULL,
  `Thecase_User_idUser` INT NOT NULL,
  PRIMARY KEY (`Keyword_idKeyword`, `Thecase_idThecase`, `Thecase_Pumpelement_idPumpelement`, `Thecase_Pumpmodel_idPumpmodel`, `Thecase_User_idUser`),
  INDEX `fk_Keyword_has_Thecase_Thecase1_idx` (`Thecase_idThecase` ASC, `Thecase_Pumpelement_idPumpelement` ASC, `Thecase_Pumpmodel_idPumpmodel` ASC, `Thecase_User_idUser` ASC),
  INDEX `fk_Keyword_has_Thecase_Keyword1_idx` (`Keyword_idKeyword` ASC),
  CONSTRAINT `fk_Keyword_has_Thecase_Keyword1`
    FOREIGN KEY (`Keyword_idKeyword`)
    REFERENCES `ProDevCBR`.`Keyword` (`idKeyword`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Keyword_has_Thecase_Thecase1`
    FOREIGN KEY (`Thecase_idThecase` , `Thecase_Pumpelement_idPumpelement` , `Thecase_Pumpmodel_idPumpmodel` , `Thecase_User_idUser`)
    REFERENCES `ProDevCBR`.`Thecase` (`idThecase` , `Pumpelement_idPumpelement` , `Pumpmodel_idPumpmodel` , `User_idUser`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

score 0 · Answer 1 · edited May 23 '17 at 12:24

For those how might end up here :

Check that the fields linked by the constraint have the exact same type
Check that the field which has a foreign key is in a index in which it is the first field
Forward engineer in two times

For solution 3 :

Manually create the fields which must have a foreign key constraint in MySQL WB
Forward engineer to your DB
Manually create the foreign key constraints in MySQL WB
Forward engineer angain

Consider using only integer key in order to avoid this kind of issue.

Duplicate :

Error 1215: Cannot add foreign key constraint

MySQL Workbench Forward Engineer Error 1215: Cannot add foreign key constraint

MYSQL Workbench - ERROR: Error 1215: Cannot add foreign key constraint

MySQL Error 1215: Cannot add foreign key constraint

MySQL error 1215 Cannot add Foreign key constraint - FK in different tables

MySQL Workbench Datamodell Error 1215: Cannot add foreign key constraint

1 Answers1