这是我的sqlite代码。
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")
);
然后按特定顺序插入以下内容:
INSERT INTO performance(title,date,theaterID)
VALUES("The Godfather", 20200230, 9);
INSERT INTO reservation(userName,reservationID,date,theaterID)
VALUES("user1", 1 , 20200230, 9);
一切正常,直到我尝试插入预订。我收到以下错误:
“外键不匹配错误-引用“性能”的“保留”
我似乎找不到原因?我必须做些什么改变?
你有:
FOREIGN KEY("date") REFERENCES "performance"("date"),
但是,主键上performance
包含三个部分:
PRIMARY KEY("title", "date", "theaterID"),
您需要在外键声明中以正确的顺序引用所有三个:
FOREIGN KEY("date") REFERENCES "performance"("title", "date", "theaterID"),
但是,"title"
不在表中,因此您必须添加它。
或者,只需将自动递增的主键添加到该键"performance"
并将其用作参考。
此外,请删除双引号。它们只会使SQL更加难以编写和读取。而且答案更难写。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句