Thread: foreign key problem
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
CASCADEON DELETE CASCADE, FOREIGN KEY (rev, table1_id) REFERENCES table1 ON UPDATE CASCADE
ONDELETE 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?
Laurette, > How is this possible? I've tried to reproduce this, but haven't been able > to yet. This has happened to use several times. Question 1: Can you experimentally create a record in table1, a matching record in table 2, and then delete the record in table1? It's possible that your "ON DELETE CASCADE" trigger got wiped out somehow. -- -Josh BerkusAglio Database SolutionsSan Francisco
Laurette, > How is this possible? I've tried to reproduce this, but haven't been able > to yet. This has happened to use several times. Oh, forgot question 2: SELECT version()? -- -Josh BerkusAglio Database SolutionsSan Francisco
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.
7.2.3. It's a mystery as to how this happened but there is a program we use to copy rows from one version to another. For some reason the person who wrote it disabled triggers before copying rows to a new version then re-enabled the triggers. If someone made changes while the triggers were off then this could happen. At least that's my story and I'm sticking to it. So, the program has been fixed and we hope this plugs the hole that that rabbit went down. Thanks, L. On Sun, 6 Oct 2002, Josh Berkus wrote: > Laurette, > > > How is this possible? I've tried to reproduce this, but haven't been able > > to yet. This has happened to use several times. > > Oh, forgot question 2: > SELECT version()? > > -- 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?