Message-ID: <003b01c115ef$b8e70740$0201a8c0@aschnabel.homeip.net>
From: A_Schnabel@t-online.de (Andre Schnabel)
To: "Steve SAUTETNER" <ssa@informactis.com>, <pgsql-general@postgresql.org>
References: <BKEHJDOFOAHJBCOBCHJGKEHACCAA.ssa@informactis.com>
Subject: Re: concurent updates
Date: Thu, 26 Jul 2001 18:26:27 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hi,

if you define the foreign key with "ON UPDATE CASCADE" you don't have to
worry about updating table2.
Would look like this:

the tables:
create table table1 (id int primary key, col1 int);
create table table2 (id int primary key references table1(id) ON UPDATE
CASCADE, col2 int);

the (one and only) UPDATE:
update table1 set id = 1001 where id = 1;

Your 2nd UPDATE will be done automatically.


The other solution for your problem is less elegant.
First INSERT a new record ( 1001 , x) for each row (1, x) in table1 into
table1
then UPDATE table2
last DELETE all records (1, x) from table1

Would look like:
BEGIN;
INSERT INTO table1 SELECT 1001, col1 FROM table1 WHERE id=1;
UPDATE table2 set id = 1001 WHERE id = 1;
DELETE FROM table1 where id=1;
COMMIT;


Hope this would help,
Andre

----- Original Message -----
From: Steve SAUTETNER
To: pgsql-general@postgresql.org
Sent: Thursday, July 26, 2001 11:19 AM
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)