Why do I get foreign key mismatch?

Question

I can't figure out why I get a foreign key mismatch with the sqlite below:

PRAGMA foreign_keys=ON;

CREATE TABLE a (
    id INT NOT NULL,
    PRIMARY KEY (id));

CREATE TABLE b (
    a_id INT NOT NULL,
    id INT NOT NULL,
    PRIMARY KEY (a_id, id),
    FOREIGN KEY (a_id) REFERENCES a(id));

CREATE TABLE c (
    b_id INT NOT NULL,
    id INT NOT NULL,
    PRIMARY KEY (b_id, id),
    FOREIGN KEY (b_id) REFERENCES b(id));

insert into a VALUES (1);
insert into b VALUES (1, 2);
insert into c VALUES (2, 3);

The last line causes:

Error: foreign key mismatch - "c" referencing "b"

What am I doing wrong?

Answer 1

The definition of b is:

CREATE TABLE b (
    a_id INT NOT NULL,
    id INT NOT NULL,
    **PRIMARY KEY (a_id, id),**
    FOREIGN KEY (a_id) REFERENCES a(id)
);

You have defined a composite primary key. That is, the primary key has more than one column in it. Any reference needs to use all the keys that are defined. So you need an a_id for the reference:

CREATE TABLE c (
    b_id INT NOT NULL,
    id INT NOT NULL,
    a_id INT,
    PRIMARY KEY (b_id, id),
    FOREIGN KEY (a_id, b_id) REFERENCES b(a_id, id)
);

This is one of the reasons why I find composite primary keys to be cumbersome. You can define a synthetic primary key for any table -- basically, an auto-incremented column. This is then suitable for a foreign key reference.