Self-referencing and inherited table

[Anastasios Hatzis <ahatzis@xxxxxxx>]

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