Re: Self-referencing and inherited table - Mailing list pgsql-general

On Tue, 4 Apr 2006, Anastasios Hatzis wrote:

> Hello!
>
> I want to realize some kind of parent-child relation with-in a table,
> but have problems with foreign key / references. Probably this issue
> occurs because I use inheritance (as implied by some pages, I found).

Probably.  If the matching row was actually in Party or Organization, it
won't be considered as satisifying the constraint.

In other words, the following happens:
sszabo=# insert into "SBObject" values (1,now(), NULL);
INSERT 160212 1
sszabo=# insert into "Party" values (2,now(),1,'a');
INSERT 160213 1
sszabo=# insert into "Party" values (3,now(),2,'a');
ERROR:  insert or update on table "Party" violates foreign key constraint
"ownerOfObject"
DETAIL:  Key (objectOwner)=(2) is not present in table "SBObject".

The first succeeds because the referenced row is in SBObject, the second
fails because it's in Party.

> In the mailing list archive I couldn't find appropriate solutions, but
> maybe I'm just blind and it's sooo easy. So I want to ask you, if you
> can support me on this issue.

There's no easy solution, sadly. The best that I know of right now is
using an external table to store the keys and having all the various
tables in the hierarchy reference that. The schema below also doesn't
guarantee unique objectIDs so you may want to change it anyway (each table
is unique, but it's not guaranteed unique between tables if people insert
their own values rather than using the default).

-- after deleting the tables again
sszabo=# insert into "SBObject" values (1,now(), NULL);
INSERT 160216 1
sszabo=# insert into "Party" values (1,now(),1,'a');
INSERT 160217 1

Inheritance needs alot of work. (I really need a macro key on my keyboard
for that phrase).

> ERROR: insert or update on table "Organization" violates foreign key
> constraint "ownerOfObject"
> DETAIL: Key (objectOwner)=(1) is not present in table "SBObject".
>
>
> Please note also, that the referenced row (here objectID = 1) was
> existing at the moment of the insert statement. However, I wonder, why
> in the DETAIL line, it says "Key (objectOwner)=(1)" ? Shouldn't it be
> the local name "(objectID)=(1)"?

IIRC, the key shown is the one in the insert, so you know which columns of
the originally acted upon row were failing.  The message is a bit wierd,
though, yeah.

> CREATE TABLE "SBObject"
> (
>    "objectID" int8 NOT NULL DEFAULT
> nextval('"SBObject_objectID_seq"'::regclass),
>    "createdOn" timestamp NOT NULL DEFAULT now(),
>    "objectOwner" int8,
>    CONSTRAINT "SBObject_pkey" PRIMARY KEY ("objectID")
> ) WITH OIDS;
> ALTER TABLE "SBObject" OWNER TO myuser;
> ALTER TABLE "SBObject" ADD CONSTRAINT "ownerOfObject" FOREIGN KEY
> ("objectOwner")
>    REFERENCES "SBObject" ("objectID") MATCH SIMPLE
>    ON UPDATE NO ACTION ON DELETE CASCADE;
>
>
> CREATE TABLE "Party"
> (
>    "objectID" int8 NOT NULL DEFAULT
> nextval('"SBObject_objectID_seq"'::regclass),
>    "createdOn" timestamp NOT NULL DEFAULT now(),
>    "objectOwner" int8,
>    "autoName" text,
>    CONSTRAINT "Party_pkey" PRIMARY KEY ("objectID")
> ) INHERITS ("SBObject")
> WITH OIDS;
> ALTER TABLE "Party" OWNER TO myuser;
> ALTER TABLE "Party" ADD CONSTRAINT "ownerOfObject" FOREIGN KEY
> ("objectOwner")
>    REFERENCES "SBObject" ("objectID") MATCH SIMPLE
>    ON UPDATE NO ACTION ON DELETE CASCADE;
>
>
> CREATE TABLE "Organization"
> (
>    "objectID" int8 NOT NULL DEFAULT
> nextval('"SBObject_objectID_seq"'::regclass),
>    "createdOn" timestamp NOT NULL DEFAULT now(),
>    "autoName" text,
>    "orgName" text,
>    "objectOwner" int8,
>    CONSTRAINT "Organization_pkey" PRIMARY KEY ("objectID")
> ) INHERITS ("Party")
> WITH OIDS;
> ALTER TABLE "Organization" OWNER TO myuser;
> ALTER TABLE "Organization" ADD CONSTRAINT "ownerOfObject" FOREIGN KEY
> ("objectOwner")
>    REFERENCES "SBObject" ("objectID") MATCH SIMPLE
>    ON UPDATE NO ACTION ON DELETE CASCADE;