MySQL报错‘Foreign key constraint is incorrectly formed’，创建Reservation表遇问题

Hey there! Let's break down why you're hitting this error and how to fix it. Looking at your table creation code, the main issue is how you're defining the foreign keys to the Showings table.

What's Going Wrong? #

You're trying to create three separate foreign keys pointing to individual columns in Showings (Movie_Title, Theatre_No, Complex_Name), but this doesn't work if Showings uses a composite primary key (which it almost certainly does, given that your Reservation table's primary key includes those same fields plus Start_Time). Foreign keys that reference composite keys need to target the entire set of columns in the parent table's primary key, not individual columns one by one.

How to Fix It #

Here's the corrected SQL code, with the foreign key properly set up to reference the full composite key of 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),
    -- Correct composite foreign key referencing the full primary key of Showings
    FOREIGN KEY (Movie_Title, Theatre_No, Complex_Name, Start_Time)
        REFERENCES Showings(Movie_Title, Theatre_No, Complex_Name, Start_Time)
);

Extra Checks to Ensure It Works #

• Double-check that Showings actually has a composite primary key made up of Movie_Title, Theatre_No, Complex_Name, and Start_Time. If its primary key is different, adjust the foreign key columns to match exactly.
• Verify that the data types of these columns in Reservation are identical to those in Showings (e.g., same varchar length, int vs. bigint, datetime vs. datetime2). Mismatched types will also throw this error.
• Make sure the Showings table exists in your database before creating Reservation—you can't reference a table that hasn't been created yet.

内容的提问来源于stack exchange，提问作者Jiahao Zhao