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

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表匹配。我正在使用DB浏览器的SQLite版本3.34.1

Answer 1

代码的问题在于，fname和lname的 bb 未定义为unique> uniquar。
随着此更改：

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

它将

” ，在您的情况下，适当的设计将是在qr中定义一个整数列引用列SID bb而不是复合外键：

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