MySQL“外键约束不正确地形成". [英] MySQL "Foreign key constraint is incorretly formed"

问题描述

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

I am having trouble with this code.

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

如果我摆脱了这一行，它就可以正常运行.

if i get rid of this line, it runs fine.

FOREIGN KEY (Start_Time) REFERENCES Showings(Start_Time),

所引用的表(显示)如下；

The referenced Table (Showings) is as follows;

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

I am using MariaDB 10.1.3

对不起，如果最终这是一个愚蠢的错误，我对SQL还是很陌生 请，谢谢.

Sorry, if this ends up being a silly mistake, i am fairly new with SQL Please and thank you.

推荐答案

外键

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

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.

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

Although this is the MySQL documentation, it likely is the same reason it fails on your version of MariaDB.

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

You can fix this by simply adding an index to the Start_Time column in the Showings table.

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

However, before you do that, consider what exactly you are trying to do with this foreign key. You are referencing ANY Start_Time in the Showings table when you create it, rather than any specific showing.

根据您的表设计，您更有可能尝试为Showings表的主键创建外键.

Based on your table design, making a foreign key to the Showings table's primary key is more likely what you are trying to achieve.

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

这篇关于MySQL“外键约束不正确地形成".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！