如何使用引用复合主键的复合外键将行插入到表中

Question

我有一个名为BB的表:

CREATE TABLE BB (
SID INTEGER PRIMARY KEY,
FName TEXT,
LName TEXT
);

还有一个名为QR的表，它包含一个复合外键，引用BB表上的FName和LName行。

CREATE TABLE QR (
QID INTEGER PRIMARY KEY AUTOINCREMENT,
FName TEXT,
LName TEXT,
DateTime_IN TEXT,
Sid4 INTEGER,
Mode TEXT,
FOREIGN KEY (FName, LName) REFERENCES BB (FName, LName)
);

和BB表填充以下数据:

INSERT INTO BB (SID, FName, LName) VALUES
(12345678, "R", "B"),
(23456789, "X", "C"),
(34567890, "M", "S"),
(45678901, "Z", "H"),
(56789012, "T", "A"),
(67890123, "N", "F"),
(78901234, "H", "M"),
(89012345, "A", "S"),
(90123456, "F", "F"),
(12345677, "M", "M");

并且需要将此数据插入QR表:

INSERT INTO QR
(FName, LName, DateTime_IN, Sid4, Mode)
VALUES
("R", "B", "2021-08-10 14:00:02", 12345678, "ZOOM"),
("X", "C", "2021-08-10 14:00:02", 23456789, "ZOOM"),
("M", "S", "2021-08-10 14:00:03", 34567890, "ZOOM"),
("Z", "H", "2021-08-10 15:01:01", 45678901, "ZOOM"),
("T", "A", "2021-08-10 14:01:01", 56789012, "ZOOM"),
("N", "F", "2021-08-10 14:02:02", 67890123, "ZOOM"),
("H", "M", "2021-08-10 14:03:03", 78901234, "ZOOM"),
("A", "S", "2021-08-11 14:04:04", 89012345, "ZOOM");

但是当我在DB浏览器中运行它时，我得到这个错误:

Execution finished with errors.
Result: foreign key mismatch - "QR" referencing "BB"
At line 1:
INSERT INTO QR
(FName, LName, DateTime_IN, Sid4, Mode)
VALUES
("R", "B", "2021-08-10 14:00:02", 12345678, "ZOOM"),
("X", "C", "2021-08-10 14:00:02", 23456789, "ZOOM"),
("M", "S", "2021-08-10 14:00:03", 34567890, "ZOOM"),
("Z", "H", "2021-08-10 15:01:01", 45678901, "ZOOM"),
("T", "A", "2021-08-10 14:01:01", 56789012, "ZOOM"),
("N", "F", "2021-08-10 14:02:02", 67890123, "ZOOM"),
("H", "M", "2021-08-10 14:03:03", 78901234, "ZOOM"),
("A", "S", "2021-08-11 14:04:04", 89012345, "ZOOM");

为什么它不让我插入这个数据?所有数据正确，与BB表匹配。我正在使用SQLite版本3.34.1与DB浏览器

Answer 1

您的代码的问题是父表BB的FName和LName的组合未定义为UNIQUE。
随着此更改:

CREATE TABLE BB (
SID INTEGER PRIMARY KEY,
FName TEXT,
LName TEXT, 
UNIQUE(FName, LName)
);

但是，在您的情况下，正确的设计是在QR中定义一个引用BB的SID列的整数列，而不是复合外键:

CREATE TABLE QR (
QID INTEGER PRIMARY KEY AUTOINCREMENT,
SID INTEGER REFERENCES BB (SID),
DateTime_IN TEXT,
Sid4 INTEGER,
Mode TEXT
);