crazygravy89 crazygravy89 - 3 years ago 177
SQL Question

Postgres many to many relationship on same base table

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

Answer Source

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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download