Foreign key mismatch when insert (SQLite)

Ask Time：2020-02-20T08:29:43 Author：Gullit

So here's my sqlite code..

CREATE TABLE "performance" (
    "title" TEXT,
    "date"  date,
    "theaterID" INTEGER,    

PRIMARY KEY("title","date","theaterID"),

    FOREIGN KEY("title") REFERENCES "movies"("title"),
    FOREIGN KEY("theaterID") REFERENCES "theater"("theaterID")
);

CREATE TABLE "reservation" (
    "userName"  TEXT,
    "reservationID" INTEGER auto_increment,
    "date"  date,
    "theaterID" INTEGER,
    PRIMARY KEY("userName","reservationID","date","theaterID"),
    FOREIGN KEY("date") REFERENCES "performance"("date"),
    FOREIGN KEY("userName") REFERENCES "user"("userName"),
    FOREIGN KEY("theaterID") REFERENCES "theater"("theaterID")
);

And I make following inserts in specific order:

INSERT INTO performance(title,date,theaterID) 
VALUES("The Godfather", 20200230, 9);



INSERT INTO reservation(userName,reservationID,date,theaterID)
VALUES("user1", 1 , 20200230, 9);

Everything works until I try to insert a reservation. I get the following error:

"foreign key mismatch error - 'reservation' referencing 'performance'"

I can't seem to find the reason for it? What changes do I have to do?

Author:Gullit，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/60311288/foreign-key-mismatch-when-insert-sqlite

Gordon Linoff :

You have:\n\nFOREIGN KEY(\"date\") REFERENCES \"performance\"(\"date\"),\n\n\nHowever, the primary key on performance has THREE parts:\n\nPRIMARY KEY(\"title\", \"date\", \"theaterID\"),\n\n\nYou need to reference all three -- in the correct order -- in the foreign key declaration:\n\nFOREIGN KEY(\"date\") REFERENCES \"performance\"(\"title\", \"date\", \"theaterID\"),\n\n\nHowever, \"title\" is not in the table, so you have to add that.\n\nOR, just add an auto-incrementing primary key to \"performance\" and use that for the reference.\n\nAlso, drop the double quotes. They just make SQL harder to write and read. And answers harder to write.",

2020-02-20T00:47:21

Foreign key mismatch when insert (SQLite)