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