Обсуждение: Self-referencing and inherited table
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). 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.
First of all, I use a table "SBObject" for general columns common for
all business objects (entries/rows) I need to store in the database. For
each model class I plan to use a table which inherits from SBObject and
usually has additional columns. Inheritance may apply over several
tables (t3 inherits from t2, t2 from t1, t1 from "SBObject").
SBObject has a column "objectID" int8 as primary key (bigserial). Beside
other columns it should have also a column "ownerObject" which should be
a reference to another row in the same table "SBObject". Replace "owner"
by parent if you want. Since the db client is handling, which object
type can be owner of which types, I want to keep ownership referencing
in the base table "SBObject".
Purpose is to realize a flat, unambigious hierarchy including cascade
delete if a parent (owner) row is deleted: all rows referencing to this
owner row should also be deleted. In addition only existing rows must be
referenced. Thus I've thought that a constraint foreign key would be
fine, but all earlier attempts at the end failed. Like the last one,
listed below, they results into the following error message (and it
doesn't matter if I insert a row into "SBObject" table or like here into
"Organization" table):
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)"?
Here is the example SQL listing. I stripped off all the statements which
I'm sure will not impact the issue (please note that I make heavy use of
case-sensitive names, sorry!):
PostgreSQL 8.1.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special), and usually with pgAdmin III (v. 1.4.1, Dec 10 2005).
CREATE DATABASE "my-database"
WITH ENCODING='UTF8'
OWNER=myuser
TEMPLATE=template1;
CREATE SEQUENCE "SBObject_objectID_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE "SBObject_objectID_seq" OWNER TO myuser;
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;
That's it. Many thanks for reading my posting. If anyone has a good
idea, I would be happy if you let me know.
Anastasios
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;