Re: foreign Key problem

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo(at)megazone23(dot)bigpanda(dot)com]
> Sent: 24 June 2002 00:45
> To: Dave Page
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] foreign Key problem
>
> > Anyway, here's a simple test case illustrating this problem.
> >
> > CREATE TABLE t1 (t1_id int4 PRIMARY KEY);
> > CREATE TABLE t2 (t2_id int4 PRIMARY KEY) INHERITS(t1);
> > CREATE TABLE t3 (t3_id int4 PRIMARY KEY);
> > CREATE TABLE t4 (
> > t4_id1 int4,
> > t4_id2 int4,
> > CONSTRAINT t4_t1 FOREIGN KEY (t4_id1) REFERENCES t1 (t1_id) ON
> > DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
> > CONSTRAINT t4_t3 FOREIGN KEY (t4_id2) REFERENCES t3 (t3_id) ON
> > DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY
> IMMEDIATE );
> >
> > INSERT INTO t2 (t1_id, t2_id) VALUES (1, 2);
> > INSERT INTO t3 (t3_id) VALUES (3);
> > INSERT INTO t4 (t4_id1, t4_id2) VALUES (1, 3);
> >
> > The error also occurs in 2 day old 7.3 dev code.
>
> Foreign keys don't inherit across tables currently, so when
> t4 references t1, it references *only* t1. It won't see rows
> from t2. Alot of things don't inherit "properly" right now
> (also note that the primary key on t1_id doesn't inherit so,
> you don't even have guaranteed uniqueness across t1_id). In
> general inheritance needs some work.

Yes, that was roughly the conclusion that I've been coming to over the
weekend. It's annoying 'cos I always thought that inheritance was one of
the big PostgreSQL pluses and the first time I find a good use for the
feature it fails me bigtime :-(

Oh well, thanks for the help anyway.

Regards, Dave.

On Mon, 24 Jun 2002, Dave Page wrote:

> > Foreign keys don't inherit across tables currently, so when
> > t4 references t1, it references *only* t1. It won't see rows
> > from t2. Alot of things don't inherit "properly" right now
> > (also note that the primary key on t1_id doesn't inherit so,
> > you don't even have guaranteed uniqueness across t1_id). In
> > general inheritance needs some work.
>
> Yes, that was roughly the conclusion that I've been coming to over the
> weekend. It's annoying 'cos I always thought that inheritance was one of
> the big PostgreSQL pluses and the first time I find a good use for the
> feature it fails me bigtime :-(

Inheritance needs a few champions who are willing to put time into making
sure that it gets to the point where it has all these features or at
least to help spec out what all these features should do.

As a side note, if you're willing to do a few extra inserts, there are
some workarounds for the foreign key case involving another table
where you get ids from there and reference that table from each of the
tables in the hierarchy and tables that want to reference the hierarchy
reference that table.