Обсуждение: Update of foreign key values
I have two tables in two databases (Pg 7.2.1 - yes I need to upgrade but there are several other dependencies I have to resolve first) and I need to update one database's tables so they can be merged into the other database's table. I know I can drop the constraints and update the tables (primary key, and foreign key) but was hoping I'd not have to do that. An attempt at using the techniques in Joel Burton's "Referential Integrity Tutorial & Hacking the Referential Integrity tables" was unsuccessful. Is there a method I can use to add 1000 to all the primary and foreign keys in one pass? Searches using Google and the PostgreSQL docs turned up nothing useful to my situation. I see if the table/constraint would have been created differently the updates would have cascaded but that did not happen. TIA, Rod -- "Open Source Software - Sometimes you get more than you paid for..."
On Mon, 2003-08-11 at 14:04, Roderick A. Anderson wrote: > I have two tables in two databases (Pg 7.2.1 - yes I need to upgrade but > there are several other dependencies I have to resolve first) and I need > to update one database's tables so they can be merged into the other > database's table. I know I can drop the constraints and update the tables > (primary key, and foreign key) but was hoping I'd not have to do that. > An attempt at using the techniques in Joel Burton's "Referential > Integrity Tutorial & Hacking the Referential Integrity tables" was > unsuccessful. > > Is there a method I can use to add 1000 to all the primary and foreign > keys in one pass? Searches using Google and the PostgreSQL docs turned up > nothing useful to my situation. I see if the table/constraint would have > been created differently the updates would have cascaded but that did > not happen. Maybe this will do it: http://www.postgresql.org/docs/7.3/static/sql-set-constraints.html begin; set constraint foo DEFERRED; update parent_table; update child_table; commit; -- +---------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "Man, I'm pretty. Hoo Hah!" | | Johnny Bravo | +---------------------------------------------------------------+
On 11 Aug 2003, Ron Johnson wrote: > Maybe this will do it: > http://www.postgresql.org/docs/7.3/static/sql-set-constraints.html Saw this but my take was it required the original constraint to be created with the deferred(able) option. But hey, I'll give it a try since all my other attempts have increased the number of barley pops I'll _have_ to drink tonight! One more won't hurt (well maybe in the morning.) > begin; > set constraint foo DEFERRED; > update parent_table; > update child_table; > commit; Rod -- "Open Source Software - Sometimes you get more than you paid for..."
On Mon, 2003-08-11 at 18:40, Roderick A. Anderson wrote: > On 11 Aug 2003, Ron Johnson wrote: > > > Maybe this will do it: > > http://www.postgresql.org/docs/7.3/static/sql-set-constraints.html > > Saw this but my take was it required the original constraint to be created > with the deferred(able) option. But hey, I'll give it a try since all my Where do you see that? "or INITIALLY IMMEDIATE NOT DEFERRABLE. The third class is not affected by the SET CONSTRAINTS command."??? > other attempts have increased the number of barley pops I'll _have_ to > drink tonight! One more won't hurt (well maybe in the morning.) > > > begin; > > set constraint foo DEFERRED; > > update parent_table; > > update child_table; > > commit; > > > Rod -- +---------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "Man, I'm pretty. Hoo Hah!" | | Johnny Bravo | +---------------------------------------------------------------+
On 11 Aug 2003, Ron Johnson wrote: > On Mon, 2003-08-11 at 18:40, Roderick A. Anderson wrote: > > On 11 Aug 2003, Ron Johnson wrote: > > > > > Maybe this will do it: > > > http://www.postgresql.org/docs/7.3/static/sql-set-constraints.html > > > > Saw this but my take was it required the original constraint to be created > > with the deferred(able) option. But hey, I'll give it a try since all my > > Where do you see that? "or INITIALLY IMMEDIATE NOT DEFERRABLE. The > third class is not affected by the SET CONSTRAINTS command."??? Yes. Either DEFERRABLE or INITIALLY DEFERRED must be given in order for set constraints to be meaningful. This might be another good place to consider a little clarification (or maybe a doc note in the interactive docs)
On Mon, 11 Aug 2003, Stephan Szabo wrote: > Yes. Either DEFERRABLE or INITIALLY DEFERRED must be given in order for > set constraints to be meaningful. This might be another good place to > consider a little clarification (or maybe a doc note in the interactive > docs) Phew. I thought I was going bonkers. Then add that I never named the constraints so I was still out'a luck. Rod -- "Open Source Software - Sometimes you get more than you paid for..."
On Tue, 12 Aug 2003, Roderick A. Anderson wrote: > On Mon, 11 Aug 2003, Stephan Szabo wrote: > > > Yes. Either DEFERRABLE or INITIALLY DEFERRED must be given in order for > > set constraints to be meaningful. This might be another good place to > > consider a little clarification (or maybe a doc note in the interactive > > docs) > > Phew. I thought I was going bonkers. Then add that I never named the > constraints so I was still out'a luck. Well, the constraint still got an autogenerated name, so you probably could have used that, although you might end up affecting more constraints than you had meant.
Yuup, always name constraints so it's easier to remove them. And if you name them meaningfully, then others might understandwhy they exist! (or later after a coffeeless morning) Roderick A. Anderson wrote: > On Mon, 11 Aug 2003, Stephan Szabo wrote: > > >>Yes. Either DEFERRABLE or INITIALLY DEFERRED must be given in order for >>set constraints to be meaningful. This might be another good place to >>consider a little clarification (or maybe a doc note in the interactive >>docs) > > > Phew. I thought I was going bonkers. Then add that I never named the > constraints so I was still out'a luck. > > > Rod
On Tue, 12 Aug 2003, Dennis Gearon wrote: > Yuup, always name constraints so it's easier to remove them. And if > you name them meaningfully, then others might understand why they > exist! (or later after a coffeeless morning) This "application" has grown as a fungus: in the dark and nourished on manure. Now I get to change/fix it. Fixing will include all those 'little' things that make life easier ... named constraints and friends. Thanks for the reply, Rod -- "Open Source Software - Sometimes you get more than you paid for..."
Roderick A. Anderson wrote: > I have two tables in two databases (Pg 7.2.1 - yes I need to upgrade but > there are several other dependencies I have to resolve first) and I need > to update one database's tables so they can be merged into the other > database's table. I know I can drop the constraints and update the tables > (primary key, and foreign key) but was hoping I'd not have to do that. > An attempt at using the techniques in Joel Burton's "Referential > Integrity Tutorial & Hacking the Referential Integrity tables" was > unsuccessful. > > Is there a method I can use to add 1000 to all the primary and foreign > keys in one pass? Searches using Google and the PostgreSQL docs turned up > nothing useful to my situation. I see if the table/constraint would have > been created differently the updates would have cascaded but that did > not happen. Strikes me as a perfect example for having the constraint defined "ON UPDATE CASCADE". Or making it "DEFERRABLE" and do both changes in a transaction after setting the constraint deferred. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #