Date: Mon, 7 Oct 2002 17:03:44 -0700 (PDT)
From: Laurette Cisneros <laurette@nextbus.com>
To: pgsql-sql@postgresql.org
Subject: foreign key problem
Message-ID: <Pine.LNX.4.44.0210071656570.19182-100000@visor.corp.nextbus.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


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?