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

Question

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

[No problem should occur](https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=20279217baeea681f02b0eb0a28b131d) — Barbaros Özhan, May 17 '22 at 12:19
Even when i run the code you have linked, which is the same as mine, and do it in that order, i get the same error when just trying to insert data into the BB table. So far all thats been done is creating the BB and QR table. It won't let me Insert the data into the BB table if the QR table exists. Ive been having to drop the QR table to insert data into the BB table. @BarbarosÖzhan — Kade Allen, May 17 '22 at 12:25

score 0 · Accepted Answer · answered May 17 '22 at 12:29

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

Adding the unique statement worked, thank you. It's for a school assignment so I cant change the rows but thanks anyway. — Kade Allen, May 17 '22 at 12:36

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

1 Answers1