mysql - 无法添加外键约束，工作台

Question

我使用 Workbench 来实现数据库模式，但在某个表中使用外键时出现此错误。

1215 - Cannot add foreign key constraint

SQL查询:

CREATE TABLE IF NOT EXISTS `Gam3ty`.`Frequently_Used_Location` (
`idFrequently_Used_Location` INT NOT NULL,
`User_idUser` INT NOT NULL,
`User_College_idCollege` INT NOT NULL,
PRIMARY KEY (`idFrequently_Used_Location`,`User_idUser`,`User_College_idCollege`),
INDEX `fk_Frequently_Used_Location_User1_idx` (`User_idUser` ASC,`User_College_idCollege` ASC),
CONSTRAINT `fk_Frequently_Used_Location_User1`
FOREIGN KEY (`User_idUser` , `User_College_idCollege`)
REFERENCES `Gam3ty`.`User` (`idUser` , `College_idCollege`)

我的 SQL:

CREATE SCHEMA IF NOT EXISTS `Gam3ty` DEFAULT CHARACTER SET utf8 ;
USE `Gam3ty` ;

-- -----------------------------------------------------
-- Table `Gam3ty`.`Frequently_Used_Location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`Frequently_Used_Location` (
`idFrequently_Used_Location` INT NOT NULL,
`User_idUser` INT NOT NULL,
`User_College_idCollege` INT NOT NULL,
PRIMARY KEY (`idFrequently_Used_Location`, `User_idUser`,          `User_College_idCollege`),
INDEX `fk_Frequently_Used_Location_User1_idx` (`User_idUser` ASC, `User_College_idCollege` ASC),
CONSTRAINT `fk_Frequently_Used_Location_User1`
FOREIGN KEY (`User_idUser` , `User_College_idCollege`)
REFERENCES `Gam3ty`.`User` (`idUser` , `College_idCollege`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Gam3ty`.`Location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`Location` (
`idLocation` INT NOT NULL,
`Frequently_Used_Location_idFrequently_Used_Location` INT NOT NULL,
`Frequently_Used_Location_User_idUser` INT NOT NULL,
`Frequently_Used_Location_User_College_idCollege` INT NOT NULL,
`type` VARCHAR(45) NULL,
PRIMARY KEY (`idLocation`),
INDEX `fk_Location_Frequently_Used_Location1_idx`  (`Frequently_Used_Location_idFrequently_Used_Location` ASC,    `Frequently_Used_Location_User_idUser` ASC, `Frequently_Used_Location_User_College_idCollege` ASC),
CONSTRAINT `fk_Location_Frequently_Used_Location1`
FOREIGN KEY (`Frequently_Used_Location_idFrequently_Used_Location` ,  `Frequently_Used_Location_User_idUser` ,        `Frequently_Used_Location_User_College_idCollege`)
 REFERENCES `Gam3ty`.`Frequently_Used_Location`         (`idFrequently_Used_Location`,  `User_idUser` , `User_College_idCollege`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Gam3ty`.`University`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`University` (
`idUniversity` INT NOT NULL,
`Location_idLocation` INT NOT NULL,
`Info` VARCHAR(45) NULL,
PRIMARY KEY (`idUniversity`, `Location_idLocation`),
INDEX `fk_University_Location1_idx` (`Location_idLocation` ASC),
CONSTRAINT `fk_University_Location1`
FOREIGN KEY (`Location_idLocation`)
REFERENCES `Gam3ty`.`Location` (`idLocation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Gam3ty`.`College`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`College` (
`idCollege` INT NOT NULL,
`University_idUniversity` INT NOT NULL,
`Location_idLocation` INT NOT NULL,
`Info` VARCHAR(45) NULL,
`Staff` VARCHAR(45) NULL,
`Department` VARCHAR(45) NULL,
PRIMARY KEY (`idCollege`, `University_idUniversity`, `Location_idLocation`),
INDEX `fk_College_University1_idx` (`University_idUniversity` ASC),
INDEX `fk_College_Location1_idx` (`Location_idLocation` ASC),
CONSTRAINT `fk_College_University1`
FOREIGN KEY (`University_idUniversity`)
REFERENCES `Gam3ty`.`University` (`idUniversity`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_College_Location1`
FOREIGN KEY (`Location_idLocation`)
REFERENCES `Gam3ty`.`Location` (`idLocation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Gam3ty`.`User`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Gam3ty`.`User` (

`idUser` INT NOT NULL,
`College_idCollege` INT NOT NULL,
`UserName` VARCHAR(45) NOT NULL,
`Password` VARCHAR(45) NOT NULL,
`E-mail` VARCHAR(45) NOT NULL,
`Social_media_accounts` VARCHAR(45) NULL,
`Gender` VARCHAR(45) NOT NULL,
`Job` VARCHAR(45) NOT NULL,
`Tel-num` BIGINT(11) NULL,
`Adress` VARCHAR(45) NULL,
PRIMARY KEY (`idUser`, `College_idCollege`, `UserName`),
INDEX `fk_User_College_idx` (`College_idCollege` ASC),
CONSTRAINT `fk_User_College`
FOREIGN KEY (`College_idCollege`)
REFERENCES `Gam3ty`.`College` (`idCollege`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

我检查了数据类型，它们都是相同的，而且每个外键都是其原始表中的主键。

Answer 1

当您运行 SQL 脚本时，表是按从上到下的顺序创建的。

您不能创建引用尚未创建的表的外键。

您必须对表进行排序，以便每个表在引用它的任何表之前创建。

---

@Rahul 写道:

You need to refer all of the columns designated as primary key.

我同意这是推荐的做法，否则您可以创建一个外键，其中给定行引用父表中的 多个 行。这导致语义不明确。例如，如果子表中有一行引用它，但父表中有第二行满足引用，您是否可以删除父表中的一行？这打破了标准 SQL 中参照完整性的定义。

尽管如此，InnoDB 允许这样做。您可以创建一个外键，该外键引用任何键(唯一或非唯一)的列的任何最左侧子集。这是一个非常糟糕的主意，但 InnoDB 允许您这样做并且不会抛出错误。

以下是疯狂的，但它不是 InnoDB 的错误:

create table foo (a int, b int, key (a, b));
create table bar (a int, foreign key (a) references foo(a));