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

"Stefan Scheidegger" <Stefan.Scheidegger@gmx.net> writes:

> Why is this not possible? It seems that this is about inheritance. When I
> reference directly to tbl_child1, everything works just fine.

I'm afraid inheritance doesn't work with foreign key references. You can have
references to specific tables but they don't include any child tables. A
related limitation is that you can't enforce unique constraints across tables
and their children.

Solving this doesn't seem to be high on anyone's priority list. If anything
what interests more people is using inheritance for partitioning support.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly