MySQL Workbench 数据模型错误 1215 : Cannot add foreign key constraint

Question

我有以下错误。我到处都在使用 InnoDB。找不到错误，因为我实际上没有改变任何东西。我只是制作了表格并在 Diagramm 中将它们一起单击。几周前它确实以这种方式工作。然后我尝试更改模型，但现在没有任何效果。

在服务器中执行 SQL 脚本ERROR: 错误 1215: 无法添加外键约束SQL代码:

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

Answer 1

对于那些可能会在这里结束的人:

1. 检查约束链接的字段是否具有完全相同的类型
2. 检查具有外键的字段是否位于第一个字段的索引中
3. 两次正向工程师

对于解决方案 3:

• 在MySQL WB中手动创建必须有外键约束的字段
• 向您的数据库 dispatch 工程师
• 在 MySQL WB 中手动创建外键约束
• 再次向前工程师

考虑仅使用整数键以避免此类问题。

重复:

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