Re: foreign Key problem

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: 24 June 2002 00:45
> To: Dave Page
> Cc: pgsql-general@postgresql.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.