Re: Cascading updates to FKs with unique constraints

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!