CODEWITHSUNDEEP
postgresql
Gabriel
Gabriel

Reputation: 5723

ERROR: there is no unique constraint matching given keys for referenced table. PG12

I'm using Postgres 12:

CREATE TABLE w_table (
    id INT PRIMARY KEY
);

CREATE TABLE l_table (
    id INT GENERATED ALWAYS AS identity,
    w_id int NOT NULL REFERENCES w_table(id),
    PRIMARY KEY (w_id, id),
    UNIQUE (w_id, id)
)PARTITION BY LIST (w_id);

CREATE TABLE t_table (
    id INT GENERATED ALWAYS AS IDENTITY,
    w_id INT REFERENCES w_table(id) NOT NULL,
    l_id INT REFERENCES l_table(id) NOT NULL,
    PRIMARY KEY (w_id,id),
    UNIQUE (w_id,id)
)PARTITION BY LIST (w_id);

And I'm getting:

ERROR: there is no unique constraint matching given keys for referenced table "l_table"

Can't figure out what am I doing wrong.

Thanks!

Upvotes: 0

Views: 142

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19665

The error is occurring because:

l_id INT REFERENCES l_table(id) NOT NULL,

is pointing at the l_id.id which is not a PRIMARY KEY or a UNIQUE constraint by itself. The PRIMARY KEY is (w_id, id) so you need to REFERENCE that combination. The w_table.id reference works because w_table.id is the PRIMARY KEY for that table. FYI, UNIQUE (w_id, id) is redundant to PRIMARY KEY (w_id, id).

Upvotes: 1

Related Questions