Hi all
I’m confronted with the following problem: I have a base table with several child tables which also use the parent’s primary key as their own primary key: CREATE TABLE tbl_parent ( p_id serial NOT NULL, p_time timestamp(6) with time zone NOT NULL DEFAULT '1970-01-01 01:00:00+01'::timestamp with time zone, CONSTRAINT tbl_parent_pkey PRIMARY KEY (p_id) ); CREATE TABLE tbl_child1 ( c1_something character varying(64) NOT NULL, CONSTRAINT tbl_child1_pkey PRIMARY KEY (p_id), ) INHERITS (tbl_parent); CREATE TABLE tbl_child2 ( c2_somethingelse integer NOT NULL, CONSTRAINT tbl_child2_pkey PRIMARY KEY (p_id), ) INHERITS (tbl_parent); This works fine so far. Now I got another table that references to the primary key of the parent (I need an n to n relation between another table and an aggregation of all my child tables, which means an n to n relation between the other table and the parent table): CREATE TABLE tbl_reference ( ref_id serial NOT NULL, ref_parent integer NOT NULL, ref_othertable integer NOT NULL, CONSTRAINT tbl_reference_pkey PRIMARY KEY (ref_id), CONSTRAINT tbl_reference_ref_parent_fkey FOREIGN KEY (ref_parent) REFERENCES tbl_parent (p_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT tbl_reference_ref_othertable_fkey FOREIGN KEY (ref_othertable) REFERENCES tbl_othertable (ot_id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL ) When I try to insert into tbl_reference now, I get an sql error: INSERT INTO tbl_child1(p_time,c1_something) VALUES ('2008-01-01', 'foo'); INSERT 0 1 SELECT * FROM tbl_parent; p_id | p_time ------+------------------------ 1 | 2008-01-01 00:00:00+01 (1 row) INSERT INTO tbl_reference(ref_parent,ref_othertable) VALUES ('1','1'); ERROR: insert or update on table "tbl_reference" violates foreign key constrain t "tbl_reference_ref_parent_fkey" DETAIL: Key (ref_parent)=(1) is not present in table "tbl_parent". Why is this not possible? It seems that this is about inheritance. When I reference directly to tbl_child1, everything works just fine. Any idea? Greets Stefan -- Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend