CODEWITHSUNDEEP
sqldatabasesqliteforeign-keys
Gullit
Gullit

Reputation: 147

Foreign key mismatch when insert (SQLite)

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?

Upvotes: 1

Views: 580

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

Maria Nazari
Maria Nazari

Reputation: 690

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

Upvotes: 1

Related Questions