mysql无法与外部健形成约束,MySQL“外键约束不正确地形成”。

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)

);

I am using MariaDB 10.1.3

Sorry, if this ends up being a silly mistake, i am fairly new with SQL

Please and thank you.

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

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

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

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.

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)

);