Re: Advice on merging two primary keys...

On Wed, 29 Jun 2005, Eric D Nielsen wrote:

> > Stephan Szabo wrote:
> > On Wed, 29 Jun 2005, Richard Huxton wrote:
> > > Eric D. Nielsen wrote:
> > > > I've come into a situation where I will often need to merge two primary
> > > > keys, with numerous foreign keys hanging off of them. For instance:
> > > > [ snip ]
> > > > While any update of the either primary key will cascade to all relevant
> > > > tables, such an update is disallowed for uniqueness reasons.
> > > >
> > > > Is there a good SQL-base method to accomplish this type of merging or
> > > > does this need application logic?
> > >
> > > It's irritating, because (afaict) the main use for cascading updates to
> > > a primary key is for merging. But, without deferred uniqueness checks
> > > you'll encounter the problem you mention. PG doesn't allow deferred
> > > uniqueness checks at the moment, so I'm afraid you'll have to explicitly
> > > update all the dependant tables.
> >
> > Deferrable unique constraints probably wouldn't actually help because you
> > cannot refer a foreign key to a deferred unique constraint. (SQL92
> > 11.8SR3) "The table constraint descriptor describing the <unique
> > constraint definition> whose <unique column list> identifies the
> > referenced columns shall indicate that the unique constraint is not
> > deferrable."
>
> Thank you both. The docs also forbid deferring the UPDATE actions
> so I don't think I could attack it from the other angle. (Not sure
> if its a spec or PostGreSQL issue, but in either case I can't see
> how it would help me in the first place.)
>
> Is there any way for the application layer (PHP in my case) to find
> out if any UPDATE CASCADE (or other UPDATE actions) would fire on a
> given query? Ie, something I could wrap in a BEGIN; ROLLBACK; block
> to act as a safety net to catch dangling references. I can't just
> change the ON DELETE behavoir from CASCADE to RESTRICT, because the
> cascading delete is a more common use case.

You could look in the system tables for foreign keys and the referential
actions. I'm wondering if maybe the easiest thing is to do the manual
dependant table updates in a merge stored procedure and just use that from
the application layer when you want to merge records.