Foreign key mismatch when insert (SQLite)

Question

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?

I've been sitting here for hours not being able to find the solution.. An answer would be highly appreciated — Gullit, Feb 20 '20 at 00:31

score 1 · Answer 1 · answered Feb 20 '20 at 00:38

check this post What is causing Foreign Key Mismatch error?

Problem could be:

The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE

score 1 · Accepted Answer · answered Feb 20 '20 at 00:47

You have:

FOREIGN KEY("date") REFERENCES "performance"("date"),

However, the primary key on performance has THREE parts:

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

You need to reference all three -- in the correct order -- in the foreign key declaration:

FOREIGN KEY("date") REFERENCES "performance"("title", "date", "theaterID"),

However, "title" is not in the table, so you have to add that.

OR, just add an auto-incrementing primary key to "performance" and use that for the reference.

Also, drop the double quotes. They just make SQL harder to write and read. And answers harder to write.

Foreign key mismatch when insert (SQLite)

2 Answers2