MySQL“外键约束形成错误" [英] MySQL "Foreign key constraint is incorretly formed"

问题描述

这段代码有问题.

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

参考表(Showings)如下；

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屋！