Re: concurent updates

Why do you need to change the value of the id field??  The id field shouldn't
have any meaning attached to it beyond the fact that it uniquely identifies a
row in the table, and of course its usage as a foreign key when it serves that
role.  If you just want to change what numbers get assigned, I think you can
update the SEQUENCE that table1 uses.

If you really need to do this, you might have better luck using a trigger to do
a cascading update from table1 to table2, and then ONLY issue the update to
table1, counting on the trigger to update table2.



"Steve SAUTETNER" <ssa%informactis.com@interlock.lexmark.com> on 07/26/2001
05:19:36 AM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] concurent updates


hi everybody !

I've got a little problem when updating a primary key in two table
where the primary key of the one is a foreign key from the second :

here are the 2 tables :

create table table1 (id int primary key, col1 int);
create table table2 (id int primary key references table1(id), col2 int);

and the 2 updates :

1) update table2 set id = 1001 where id = 1;
2) update table1 set id = 1001 where id = 1;

i can't execute them separately because of an integrity constraint
violation.
i've got the same error in a BEGIN / COMMIT block containing the updates.

Does any one see how two help me ?

thanks.


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