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 | Resend email |
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!
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 |