Thread: 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;