Re: Bug in FOREIGN KEY

hi, there!

On Mon, 22 Jan 2001, Bruce Momjian wrote:

> 
> > This problem with foreign keys has been reported to me, and I have confirmed
> > the bug exists in current sources.  The DELETE should succeed:
> > 
> > ---------------------------------------------------------------------------
> > 
> > CREATE TABLE primarytest2 (
> >                            col1 INTEGER, 
> >                            col2 INTEGER, 
> >                            PRIMARY KEY(col1, col2)
> >                           );
> > 
> > CREATE TABLE foreigntest2 (col3 INTEGER, 
> >                            col4 INTEGER,
> >                            FOREIGN KEY (col3, col4) REFERENCES primarytest2
> >                          );
> > test=> BEGIN;
> > BEGIN
> > test=> INSERT INTO primarytest2 VALUES (5,5);
> > INSERT 27618 1
> > test=> DELETE FROM primarytest2 WHERE col1 = 5 AND col2 = 5;
> > ERROR:  triggered data change violation on relation "primarytest2"

I have another (slightly different) example:
--- cut here ---
test=> CREATE TABLE pr(obj_id int PRIMARY KEY);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'pr_pkey' for
table 'pr'
CREATE
test=> CREATE TABLE fr(obj_id int REFERENCES pr ON DELETE CASCADE);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
test=> BEGIN;
BEGIN
test=> INSERT INTO pr (obj_id) VALUES (1);
INSERT 200539 1
test=> INSERT INTO fr (obj_id) SELECT obj_id FROM pr;
INSERT 200540 1
test=> DELETE FROM fr;
ERROR:  triggered data change violation on relation "fr"
test=> 
--- cut here ---

we are running postgresql 7.1 beta3

/fjoe