Re: foreign key problem

On Mon, 7 Oct 2002, Laurette Cisneros wrote:

>
> I have two tables:
>
> create table table1
>         ( vers                  integer,
>           table1_id             text NOT NULL,
>           desc                  text,
>         PRIMARY KEY (rev, table1)id)
>         );
>
> create table table2
>         ( vers                  integer,
>           othertble_id          text NOT NULL,
>           table1_id             text,
>         FOREIGN KEY (rev, othertable_id) REFERENCES othertable
>                         ON UPDATE CASCADE ON DELETE CASCADE,
>         FOREIGN KEY (rev, table1_id) REFERENCES table1
>                         ON UPDATE CASCADE ON DELETE CASCADE,
>         PRIMARY KEY (rev, othertable_id, table1_id)
>        );
>
> As you can see, table2 has a foreign key reference to table1 and also
> includes on delete cascade (and on update cascade).
>
> There has been a lot of activity in this database on these tables in
> particular in the last several days and somehow we've ended up with rows in
> table2 that have table1_ids that do not exist in table1.
>
> How is this possible?  I've tried to reproduce this, but haven't been able
> to yet.  This has happened to use several times.

The only thing apart from bugs I can think of would be triggers or rules
that forced the implicit deletes to have another behavior.  It'd be
helpful if you can get a representative sequence that reproduces it.