From: Laurette Cisneros <laurette@nextbus.com>
To: pgsql-sql@postgresql.org
Subject: foreign key problem
Date: Mon, 7 Oct 2002 17:03:44 -0700 (PDT)
Message-ID: <Pine.LNX.4.44.0210071656570.19182-100000@visor.corp.nextbus.com> (raw)
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.
Thanks for any help,
--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
------------------------------
It's 10 o'clock...
Do you know where your bus is?
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: laurette@nextbus.com
Subject: Re: foreign key problem
In-Reply-To: <Pine.LNX.4.44.0210071656570.19182-100000@visor.corp.nextbus.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