Postgres many to many relationship on same base table

Question

I'm trying to setup a many to many association on the same base table in postgres.

I'm a little stuck with this query; particularly the following error message.

Error

ERROR: insert or update on table "link" violates foreign key
constraint "link_primaryid_fkey" SQL state: 23503 Detail: Key
(primaryid)=(2) is not present in table "entity".

I would expect the

PrimaryId

column to exist in the

Link

table (Which it does). However the error seems to suggest the

PrimaryId

column also needs to exist in the base

Entity

Table which is what I'm trying to avoid.

Would anyone be able to point me in the right direction?

Script to get up to speed

--- Create Tables

CREATE TABLE "Entity" ("Id" SERIAL PRIMARY KEY);

CREATE TABLE "Task" ("Name" TEXT) INHERITS ("Entity");

CREATE TABLE "Project" ("Name" TEXT) INHERITS ("Entity");



--- Create mock data

INSERT INTO "Task" ("Name") VALUES ('Foo');

INSERT INTO "Project" ("Name") VALUES ('Bar');



-- Create Link Table

CREATE TABLE "Link" (

  "PrimaryId" INTEGER REFERENCES "Entity" ("Id")

                    ON UPDATE CASCADE

                    ON DELETE CASCADE,

  "SecondaryId" INTEGER REFERENCES "Entity"("Id" )

            ON UPDATE CASCADE

                    ON DELETE CASCADE,

  PRIMARY KEY ("PrimaryId", "SecondaryId")

);



--- Create Associations. It errors here

INSERT INTO "Link" ("PrimaryId", "SecondaryId") VALUES (2, 1)

Update 1

Updated to use absolute case (

"

) everywhere

Thanks

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Foreign keys do not work well with inheritance, as they only see the contents of the base table, not the inherited tables.

Please see https://www.postgresql.org/docs/9.6/static/ddl-inherit.html and specifically:

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms of the above example:

(...)

Specifying that another table's column REFERENCES cities(name) would allow the other table to contain city names, but not capital names. There is no good workaround for this case.

Postgres many to many relationship on same base table

Sign up