Re: foreign Key problem

On Fri, 21 Jun 2002, Dave Page wrote:

> Following up my own message...
>
> > -----Original Message-----
> > From: Dave Page
> > Sent: 21 June 2002 08:29
> > To: 'Stephan Szabo'
> > Cc: pgsql-general@postgresql.org
> > Subject: RE: [GENERAL] foreign Key problem
> >
> >
> > I'll see if I can get a test example to work (==fail) with
> > non-sensitive data as soon as England have beaten Brazil... :-)
> >
>
> Oh well, you can't win 'em all :-(
>
> 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.