mysql - hh错误 : Error 1215: Cannot add foreign key constraint: MULTI VALUED REFERENCED TABLE

Question

使用 InnoDB = 是拼写=使用工作台的关系引用(或多或少应该是正确的)主键=是

我真的不知道如何解决这个问题...我希望 PEO-MVAlign 的 SCHOOLID 引用 MVALIGN 和 PEO 的 SchoolID...但是 PEO-MVALIGN 表中应该只能看到或存在一个 SCHOOL ID。 .

错误消息

Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
        CREATE TABLE IF NOT EXISTS `mydb`.`PEO-MVAlign` (
          `SchoolID` DECIMAL(5) NOT NULL,
          `values` CHAR(25) NOT NULL,
          `DegreeProgram` VARCHAR(50) NOT NULL,
          `PEOCode` CHAR(4) NOT NULL,
          PRIMARY KEY (`SchoolID`, `values`, `DegreeProgram`, `PEOCode`),
          INDEX `fk_PEO-MVAlign_MVAlign1_idx` (`SchoolID` ASC, `values` ASC),
          INDEX `fk_PEO-MVAlign_PEO1_idx` (`SchoolID` ASC, `DegreeProgram` ASC, `PEOCode` ASC),
          CONSTRAINT `fk_PEO-MVAlign_MVAlign1`
            FOREIGN KEY (`SchoolID` , `values`)
            REFERENCES `mydb`.`MVAlign` (`SchoolID` , `values`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_PEO-MVAlign_PEO1`
            FOREIGN KEY (`DegreeProgram` , `PEOCode` , `SchoolID`)
            REFERENCES `mydb`.`PEO` (`DegreeProgram` , `PEOCode` , `SchoolID`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

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

Fetching back view definitions in final form.
Nothing to fetch

代码

-- MySQL Workbench Forward Engineering

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 mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;

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

-- -----------------------------------------------------
-- Table `mydb`.`REF-School`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`REF-School` ;

CREATE TABLE IF NOT EXISTS `mydb`.`REF-School` (
  `SchoolID` DECIMAL(5) NOT NULL,
  `SchoolName` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`SchoolID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`MVAlign`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`MVAlign` ;

CREATE TABLE IF NOT EXISTS `mydb`.`MVAlign` (
  `SchoolID` DECIMAL(5) NOT NULL,
  `values` CHAR(25) NOT NULL,
  PRIMARY KEY (`SchoolID`, `values`),
  INDEX `fk_MVAlign_REF-School` (`SchoolID` ASC),
  CONSTRAINT `fk_MVAlign_REF-School`
    FOREIGN KEY (`SchoolID`)
    REFERENCES `mydb`.`REF-School` (`SchoolID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`REF-Degree`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`REF-Degree` ;

CREATE TABLE IF NOT EXISTS `mydb`.`REF-Degree` (
  `SchoolID` DECIMAL(5) NOT NULL,
  `DegreeProgram` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`SchoolID`, `DegreeProgram`),
  INDEX `fk_REF-Degree_REF-School1` (`SchoolID` ASC),
  CONSTRAINT `fk_REF-Degree_REF-School1`
    FOREIGN KEY (`SchoolID`)
    REFERENCES `mydb`.`REF-School` (`SchoolID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`REF-Courses`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`REF-Courses` ;

CREATE TABLE IF NOT EXISTS `mydb`.`REF-Courses` (
  `SchoolID` DECIMAL(5) NOT NULL,
  `DegreeProgram` VARCHAR(50) NOT NULL,
  `CourseCode` VARCHAR(25) NOT NULL,
  `Unit` DECIMAL(2,1) NOT NULL,
  `CourseDescription` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`SchoolID`, `DegreeProgram`, `CourseCode`),
  INDEX `FK_COURSEStoDEGREE` (`SchoolID` ASC, `DegreeProgram` ASC),
  CONSTRAINT `FK_COURSEStoDegree`
    FOREIGN KEY (`SchoolID` , `DegreeProgram`)
    REFERENCES `mydb`.`REF-Degree` (`SchoolID` , `DegreeProgram`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`REF-Courses`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`REF-Courses` ;

CREATE TABLE IF NOT EXISTS `mydb`.`REF-Courses` (
  `SchoolID` DECIMAL(5) NOT NULL,
  `DegreeProgram` VARCHAR(50) NOT NULL,
  `CourseCode` VARCHAR(25) NOT NULL,
  `Unit` DECIMAL(2,1) NOT NULL,
  `CourseDescription` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`SchoolID`, `DegreeProgram`, `CourseCode`),
  INDEX `FK_COURSEStoDEGREE` (`SchoolID` ASC, `DegreeProgram` ASC),
  CONSTRAINT `FK_COURSEStoDegree`
    FOREIGN KEY (`SchoolID` , `DegreeProgram`)
    REFERENCES `mydb`.`REF-Degree` (`SchoolID` , `DegreeProgram`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`IGOA`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`IGOA` ;

CREATE TABLE IF NOT EXISTS `mydb`.`IGOA` (
  `SchoolID` DECIMAL(5) NOT NULL,
  `IGOCode` CHAR(25) NOT NULL,
  `IGA` CHAR(50) NOT NULL,
  `GradOutcome` VARCHAR(500) NOT NULL,
  PRIMARY KEY (`SchoolID`, `IGOCode`),
  INDEX `FK_IGOAtoSchool` (`SchoolID` ASC),
  CONSTRAINT `FK_IGOAtoSchool`
    FOREIGN KEY (`SchoolID`)
    REFERENCES `mydb`.`REF-School` (`SchoolID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`IGO-MVAlign`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`IGO-MVAlign` ;

CREATE TABLE IF NOT EXISTS `mydb`.`IGO-MVAlign` (
  `SchoolID` DECIMAL(5) NOT NULL,
  `values` CHAR(25) NOT NULL,
  `IGOCode` CHAR(25) NOT NULL,
  PRIMARY KEY (`SchoolID`, `values`, `IGOCode`),
  INDEX `fk_IGO-MVAlign_IGOA1_idx` (`SchoolID` ASC, `IGOCode` ASC),
  INDEX `fk_IGO-MVAlign_MVAlign1_idx` (`SchoolID` ASC, `values` ASC),
  CONSTRAINT `fk_IGO-MVAlign_IGOA1`
    FOREIGN KEY (`SchoolID` , `IGOCode`)
    REFERENCES `mydb`.`IGOA` (`SchoolID` , `IGOCode`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_IGO-MVAlign_MVAlign1`
    FOREIGN KEY (`SchoolID` , `values`)
    REFERENCES `mydb`.`MVAlign` (`SchoolID` , `values`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`PEO`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`PEO` ;

CREATE TABLE IF NOT EXISTS `mydb`.`PEO` (
  `SchoolID` DECIMAL(5) NOT NULL,
  `DegreeProgram` VARCHAR(50) NOT NULL,
  `PEOCode` CHAR(4) NOT NULL,
  `EducObj` VARCHAR(500) NOT NULL,
  PRIMARY KEY (`SchoolID`, `DegreeProgram`, `PEOCode`),
  INDEX `FK_PEOtoREF-DEGREE` (`SchoolID` ASC, `DegreeProgram` ASC),
  CONSTRAINT `FK_PEOtoREF-DEGREE`
    FOREIGN KEY (`SchoolID` , `DegreeProgram`)
    REFERENCES `mydb`.`REF-Degree` (`SchoolID` , `DegreeProgram`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`PEO-MVAlign`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`PEO-MVAlign` ;

CREATE TABLE IF NOT EXISTS `mydb`.`PEO-MVAlign` (
  `SchoolID` DECIMAL(5) NOT NULL,
  `values` CHAR(25) NOT NULL,
  `DegreeProgram` VARCHAR(50) NOT NULL,
  `PEOCode` CHAR(4) NOT NULL,
  PRIMARY KEY (`SchoolID`, `values`, `DegreeProgram`, `PEOCode`),
  INDEX `fk_PEO-MVAlign_MVAlign1_idx` (`SchoolID` ASC, `values` ASC),
  INDEX `fk_PEO-MVAlign_PEO1_idx` (`SchoolID` ASC, `DegreeProgram` ASC, `PEOCode` ASC),
  CONSTRAINT `fk_PEO-MVAlign_MVAlign1`
    FOREIGN KEY (`SchoolID` , `values`)
    REFERENCES `mydb`.`MVAlign` (`SchoolID` , `values`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PEO-MVAlign_PEO1`
    FOREIGN KEY (`DegreeProgram` , `PEOCode` , `SchoolID`)
    REFERENCES `mydb`.`PEO` (`DegreeProgram` , `PEOCode` , `SchoolID`)
    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

SQL FOREIGN KEY在一个表中引用另一表中的相应列列表。 FK 和引用表之间的列对应关系不是按名称，而是按列表中列的顺序。

引用的列列表必须在引用的表中声明为 UNIQUE NOT NULL 或 PRIMARY KEY。对应列的类型必须有一定的一致性(包括整数到整数、字符串到字符串)。同样在 MySQL 中，FK 列必须有一个索引，这些列作为其第一列，且顺序相同。在带有 InnoDB 的 MySQL 下，引用的列必须在其表中具有索引，并且这些列作为其第一列，并且顺序相同。 (某些索引可以是隐式的，例如 PRIMARY KEY 或 FOREIGN KEY。)

对于 PEO-MVAlign PEO FK，引用列的顺序与引用列的顺序不同。这会将 PEO-MVAlign 和 PEO 中不同名称的列配对。这可能不是你想要的；您可能希望引用和被引用的列列表的顺序相同。但对应列的类型(都不匹配)，因此不允许对应。但即使允许它们，引用的列列表也不会与 PEO-MVAlign 或 PEO 中的索引具有相同顺序的相同名称。