[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])
Re: [GENERAL] concurent updates
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]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] concurent updates
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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 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 [EMAIL PROTECTED] on 07/26/2001 05:19:36 AM To: [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]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] concurent updates
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] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
