CODEWITHSUNDEEP
sqlsqliteforeign-keys
user9038136
user9038136

Reputation:

Why do I get foreign key mismatch?

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?

Upvotes: 2

Views: 629

Answers (3)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

A composite primary key is a primary key consisting of more than one column. in table b you have composite primary key which is b_id, id so you have to use both.You can learn about composite primary key with example: https://beginnersbook.com/2015/04/composite-key-in-dbms/

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

Upvotes: 1

Simo
Simo

Reputation: 953

b table's PK is a_id + id so your c table's FK has to be referenced not only to b(id) but also to b(a_id). I can suggest you to avoid using of composite primary keys in tables.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

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.

Upvotes: 1

Related Questions