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