Re: Cascading updates to FKs with unique constraints

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?

I think the answer is no, but thought one of the gurus on the list might
have a more creative answer. Thanks!

Brian

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!

Brian,

> I think the answer is no, but thought one of the gurus on the list might
> have a more creative answer. Thanks!

You're right. There's really no way around it except running an update on
each dependant table.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco