MYSQL 创建外部包含时出错

Question

我正在尝试为我的 uni 分配创建一个数据库，但我在代码的第 47 行遇到外键约束问题，如下所示；

CONSTRAINT Sessions_Screen_ID_FK FOREIGN KEY (ScreenID) REFERENCES screen(ScreenID),

我收到的错误是

#1215 - Cannot add foreign key constraint

我似乎无法绕过它。如果我删除约束虽然脚本运行正常。

我在下面粘贴了完整的脚本:谢谢你的时间

Drop Database IF EXISTS AE2_ISYS114;

CREATE Database AE2_ISYS114;

CREATE TABLE AE2_ISYS114.branch(
BranchID            char(2) NOT NULL,
BranchName          varchar(20),
BranchSuburb        varchar(15),
BranchPostCode      char(4),
PRIMARY KEY (BranchID)
);



CREATE TABLE AE2_ISYS114.movie(
MovieID                 char(2) NOT NULL,
MovieName               varchar(20),
MovieGenre              varchar(20),
MovieClassification     varchar(5),
PRIMARY KEY (MovieID)
);


CREATE TABLE AE2_ISYS114.screen(
ScreenID                char(2) NOT NULL,
BranchID                char(2),
ScreenCapacity          int(3),
PRIMARY KEY (BranchID,ScreenID),
CONSTRAINT Screen_Branch_ID_fk FOREIGN KEY (BranchID) REFERENCES Branch(BranchID)
);


CREATE TABLE AE2_ISYS114.sessions(
SessionID               char(4) NOT NULL,
BranchID                char(2),
ScreenID                char(2),
MovieID                 char(2),
SessionDate             DATE,
SessionPrice            Decimal(4,2),
PRIMARY KEY (SessionID),
CONSTRAINT Sessions_Branch_ID_fk FOREIGN KEY (BranchID) REFERENCES branch(BranchID),
CONSTRAINT Sessions_Screen_ID_FK FOREIGN KEY (ScreenID) REFERENCES screen(ScreenID),
CONSTRAINT Sessions_Movie_ID_fk FOREIGN KEY (MovieID) REFERENCES movie(MovieID)
);

Answer 1

问题是MySQL需要对引用的字段(screen.ScreenID)进行索引，并且引用的字段应该是索引中最左边的字段。作为 using foreign keys 上的 MySQL 文档说:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

screen.ScreenID 字段是 screen 表的主键的一部分，但是，它不是最左边的字段。您需要在 screen.ScreenID 字段上创建一个单独的索引以使外键起作用。