RE: Referential integrity problem

Please take my name off of this email list.

Thank you!

-----Original Message-----
From: pgsql-bugs(at)postgresql(dot)org [mailto:pgsql-bugs(at)postgresql(dot)org]
Sent: Monday, June 18, 2001 12:49 PM
To: pgsql-bugs(at)postgresql(dot)org
Subject: [BUGS] Referential integrity problem

Denis Sbragion (d(dot)sbragion(at)infotecna(dot)it) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Referential integrity problem

Long Description
It looks like performing operation in two steps on tables with multiple
deferred referential integrity constraints between them is not allowed. Not
sure this is a bug, may be it is required by the standards, but sure it is
rater counterintuitive (may be, of course, I'm completely wrong). Most of
the times this is just annoying, sometimes it doesn't let you do updates,
i.e. whenever you cannot perform the update in a single step (I found at
least one case). Tested both on 7.0.3 and 7.1.2, with same results.

P.S. Great job guys. 7.0.3 was very good, but 7.1.2 is simply wonderful.

Sample Code
Here it is an oversimplyfied example:

create table tablea
(
fielda integer,
fieldb varchar(128),
constraint tablea_pkey primary key (fielda)
);

create table tableb
(
fielda integer,
fieldb integer,
fieldc varchar(128),
fieldd integer,
constraint tableb_pkey primary key (fielda, fieldb),
constraint tablea_to_tableb foreign key (fielda) references
tablea (fielda) on delete restrict on update restrict
deferrable initially deferred,
constraint scndtablea_to_tableb foreign key (fieldd) references
tablea (fielda) on delete restrict on update restrict
deferrable initially deferred
);

insert into tablea (fielda, fieldb) values (1, 'Key 1');
insert into tableb (fielda, fieldb, fieldc, fieldd) values (1, 1, 'Ref to
Key 1', 1);

Performing:

begin;
update tableb set fielda = 2, fieldd = 2 where fielda = 1;
update tablea set fielda = 2 where fielda = 1;
commit;

it's ok as it should. If you do the same thing above (now reversed, of
course) in two steps, i.e:

begin;
update tableb set fielda = 1 where fielda = 2;
update tableb set fieldd = 1 where fieldd = 2;
update tablea set fielda = 1 where fielda = 2;
commit;

gives:

ERROR: scndtablea_to_tableb referential integrity violation - key
referenced from tableb not found in tablea

I hope it's clear.

No file was uploaded with this report

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)