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

Cascading updates to FKs with unique constraints

From: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Cascading updates to FKs with unique constraints
Date: 2006-03-03 00:58:26
Message-ID: 440794B2.2060909@pukkasoft.com (view raw or flat)
Thread:
Lists: sfpug
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



Responses

sfpug by date

Next:From: David FetterDate: 2006-03-03 02:12:23
Subject: Re: Cascading updates to FKs with unique constraints
Previous:From: Sean ChittendenDate: 2006-02-21 18:23:50
Subject: Re: April Postgres meeting

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