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
Views: Raw Message | Whole Thread | Download mbox
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

Browse sfpug by date

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