Re: Advice on merging two primary keys...

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