public inbox for [email protected]
help / color / mirror / Atom feedconcurent updates
5+ messages / 5 participants
[nested] [flat]
* concurent updates
@ 2001-07-26 09:19 Steve SAUTETNER <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Steve SAUTETNER @ 2001-07-26 09:19 UTC (permalink / raw)
To: pgsql-general
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: concurent updates
@ 2001-07-26 15:24 [email protected]
0 siblings, 1 reply; 5+ messages in thread
From: [email protected] @ 2001-07-26 15:24 UTC (permalink / raw)
To: Steve SAUTETNER <[email protected]>; +Cc: pgsql-general
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%[email protected]> on 07/26/2001
05:19:36 AM
To: pgsql-general%[email protected]
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 [email protected])
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: concurent updates
@ 2001-07-26 16:26 Andre Schnabel <[email protected]>
parent: Steve SAUTETNER <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Andre Schnabel @ 2001-07-26 16:26 UTC (permalink / raw)
To: Steve SAUTETNER <[email protected]>; pgsql-general
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: [email protected]
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 [email protected])
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: concurent updates
@ 2001-07-26 18:00 Stephan Szabo <[email protected]>
parent: Steve SAUTETNER <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2001-07-26 18:00 UTC (permalink / raw)
To: Steve SAUTETNER <[email protected]>; +Cc: pgsql-general
On Thu, 26 Jul 2001, Steve SAUTETNER wrote:
> 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 ?
Either on update cascade (as suggested by someone else) or making the
constraint deferred in which case you can use a begin...commit block.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: concurent updates
@ 2001-07-26 20:56 Len Morgan <[email protected]>
parent: [email protected]
0 siblings, 0 replies; 5+ messages in thread
From: Len Morgan @ 2001-07-26 20:56 UTC (permalink / raw)
To: pgsql-general
Unless you have over simplified your example, why do you have two tables?
Wouldn't:
create table table1 (id int primary key, col1 int, col2 int)
do the same thing in one table? I would think that ANY schema that has two
tables with the SAME primary key can be resolved to one table without losing
anything.
len morgan
> 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 [email protected])
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to [email protected]
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-07-26 20:56 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-26 09:19 concurent updates Steve SAUTETNER <[email protected]>
2001-07-26 16:26 ` Andre Schnabel <[email protected]>
2001-07-26 18:00 ` Stephan Szabo <[email protected]>
2001-07-26 15:24 Re: concurent updates [email protected]
2001-07-26 20:56 ` Len Morgan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox