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

Thanks for the comment wildplasser, I tried making everything using the same case and still have the same problem. Does it look like I'm still doing something wrong? — , Oct 01 '17 at 11:36
https://www.postgresql.org/docs/current/static/ddl-inherit.html#DDL-INHERIT-CAVEATS "*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.*" ... "*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.*" — melpomene, Oct 01 '17 at 11:47

score 2 · Accepted Answer · edited Oct 01 '17 at 11:49

2

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.

edited Oct 01 '17 at 11:49

melpomene

84,125
8
85
148

answered Oct 01 '17 at 11:49

jcaron

17,302
6
32
46

Ahh awesome thanks! I found some additional information based off this for anyone interested. https://stackoverflow.com/a/24361724/8703418 "The short version: you can use foreign keys, or table inheritance, but not both. This isn't inherently impossible, it's just that it's technically quite difficult to implement unique indexes that span inherited tables in PostgreSQL in a fast, reliable manner. Without that, you can't have a useful foreign key. Nobody's successfully implemented it well enough for a patch adding support to be accepted into PostgreSQL yet." – Oct 01 '17 at 12:52

Postgres many to many relationship on same base table

1 Answers1