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.