Обсуждение: REFERENCES and INHERITS restrictions?

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
timezone, 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
relationbetween another table and an aggregation of all my child tables, which means an n to n relation between the
othertable 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
PRIMARYKEY (ref_id), CONSTRAINT tbl_reference_ref_parent_fkey FOREIGN KEY (ref_parent)     REFERENCES tbl_parent (p_id)
MATCHSIMPLE     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
worksjust fine.
 

Any idea?


Greets Stefan

-- 
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten 
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser

"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!