From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Laurette Cisneros <laurette@nextbus.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: foreign key problem
Date: Mon, 7 Oct 2002 17:21:31 -0700 (PDT)
Message-ID: <20021007171922.N82652-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0210071656570.19182-100000@visor.corp.nextbus.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, laurette@nextbus.com
Subject: Re: foreign key problem
In-Reply-To: <20021007171922.N82652-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox