admin

MySQL的``外键约束不正确地形成了''。

sql

我在使用此代码时遇到麻烦。

CREATE TABLE Reservation (
    Movie_Title varchar(255) NOT NULL,
    Theatre_No int NOT NULL,
    Complex_Name varchar(255) NOT NULL,
    Start_Time datetime NOT NULL,
    CustomerID int NOT NULL,
    Number_of_Tickets int NOT NULL,
    PRIMARY KEY (Movie_Title, Theatre_No, Complex_Name, Start_Time, CustomerID),
    FOREIGN KEY (Movie_Title) REFERENCES Showings(Movie_Title),
    FOREIGN KEY (Theatre_No) REFERENCES Showings(Theatre_No),
    FOREIGN KEY (Complex_Name) REFERENCES Showings(Complex_Name),
    FOREIGN KEY (Start_Time) REFERENCES Showings(Start_Time), /* this line causes error*/
    FOREIGN KEY (CustomerID) REFERENCES Customer(Account_Number)
);

如果我摆脱了这条线,它运行正常。

FOREIGN KEY (Start_Time) REFERENCES Showings(Start_Time),

参考表(显示)如下:

CREATE TABLE Showings (
    Movie_Title varchar(255) NOT NULL,
    Theatre_No int NOT NULL,
    Complex_Name varchar(255) NOT NULL,
    Start_Time datetime NOT NULL,
    Num_Seats int NOT NULL,
    PRIMARY KEY (Movie_Title, Theatre_No, Complex_Name, Start_Time),
    FOREIGN KEY (Movie_Title) REFERENCES Movie(Title),
    FOREIGN KEY (Theatre_No) REFERENCES Theatre(Theatre_No),
    FOREIGN KEY (Complex_Name) REFERENCES Theatre(Complex_Name)
);

我正在使用MariaDB 10.1.3

抱歉,如果最终这是一个愚蠢的错误,我对SQL还是很陌生,请谢谢。


阅读 38

收藏
2021-06-07

共1个答案

admin

无法将外键添加到未建立索引的列中

MySQL要求在外键和引用键上建立索引,以便外键检查可以快速进行,而无需进行表扫描。

尽管这是MySQL文档,但可能与在您的MariaDB版本上失败相同。

您可以通过简单地向表中的Start_Time列添加索引来解决此问题Showings

但是,在执行此操作之前,请考虑您到底想使用此外键做什么。创建表时,您引用Start_Time的是Showings表中的ANY
,而不是任何特定的显示。

根据您的表设计,您Showings很可能想要实现对表主键的外键。

CREATE TABLE Reservation (
    Movie_Title varchar(255) NOT NULL,
    Theatre_No int NOT NULL,
    Complex_Name varchar(255) NOT NULL,
    Start_Time datetime NOT NULL,
    CustomerID int NOT NULL,
    Number_of_Tickets int NOT NULL,
    PRIMARY KEY (Movie_Title, Theatre_No, Complex_Name, Start_Time, CustomerID),
    FOREIGN KEY (Movie_Title) REFERENCES Showings(Movie_Title),
    FOREIGN KEY (Theatre_No) REFERENCES Showings(Theatre_No),
    FOREIGN KEY (Complex_Name) REFERENCES Showings(Complex_Name),
    FOREIGN KEY `Showing` (Movie_Title, Theatre_No, Complex_Name, Start_Time) REFERENCES Showings(Movie_Title, Theatre_No, Complex_Name, Start_Time),
    FOREIGN KEY (CustomerID) REFERENCES Customer(Account_Number)
);
2021-06-07