Re: Advice on merging two primary keys...

> 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.

If its not possible from PHP, but would be from some other language's
db access library, I can probably make that work too, if you just
point me to a useful API.

Thanks!

Eric