我有一張名為 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");
但是當我在資料庫瀏覽器中運行它時，我得到了這個錯誤：
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 Browser 的 SQLite 版本 3.34.1
uj5u.com熱心網友回復：
您的代碼的問題是父表的FName和的組合未定義為.
通過此更改：LNameBBUNIQUE
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
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/476568.html
標籤：sql sqlite 外键 sql-插入 db-浏览器-sqlite