How to insert a row into a table with a composite foreign key referencing a composite primary key-CodePudding

I have a table called BB:

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

And a table called QR that contains a composite foreign key referencing the FName and LName rows on the BB table.

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)
);

and the BB table is filled with this data:

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");

And need to insert this data into the QR table:

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");

But when i run it in DB Browser i get this error:

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");

Why won't it let me insert this data. All the data is correct and matching to the BB table. I am using SQLite version 3.34.1 with DB Browser

CodePudding user response：

The problem with your code is that the combination of FName and LName of the parent table BB is not defined as UNIQUE.
With this change:

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

it will work as it should.

But, the proper design in your case would be to define in QR an integer column referencing the column SID of BB instead of the composite foreign key:

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