Skip site navigation (1) Skip section navigation (2)

Re: Cascading updates to FKs with unique constraints

From: David Fetter <david(at)fetter(dot)org>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Cascading updates to FKs with unique constraints
Date: 2006-03-03 02:12:23
Message-ID: 20060303021223.GG10861@fetter.org (view raw or flat)
Thread:
Lists: sfpug
On Thu, Mar 02, 2006 at 04:58:26PM -0800, Brian Ghidinelli wrote:
> 
> Greets,
> 
> I have a table that holds unique accounts referenced by many tables and 
> occasionally we need to merge duplicate accounts together.  In the end, 
> we want all related records from both accounts to be merged down to a 
> single account ID.
> 
> What I would like to do is use ON UPDATE to cascade the change in the 
> account table ID to all other tables.  However, there is a unique index 
> on account IDs preventing me from something like:
> 
> UPDATE accounts SET ID = IdOfNewAccount WHERE ID = IdOfOldAccount;
> 
> where this query would generate a duplicate row in accounts.  Are there 
> any other options besides writing the queries by hand (there are a 
> decent number of dependent tables) to update the dependent IDs with 
> IdOfNewAccount?

Not exactly, but you can get such a list--newsysviews or dbvisualizer
should be able to extract one for you--and keep it updated in a stored
procedure.  Then when you want to merge the two, you do an UPDATE on
all of those tables inside a transaction.

Cheers,
D
-- 
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

In response to

sfpug by date

Next:From: Josh BerkusDate: 2006-03-03 18:45:41
Subject: Re: Cascading updates to FKs with unique constraints
Previous:From: Brian GhidinelliDate: 2006-03-03 00:58:26
Subject: Cascading updates to FKs with unique constraints

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group