Thread: Advice on merging two primary keys...

Advice on merging two primary keys...

From
"Eric D. Nielsen"
Date:
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:

CREATE TABLE people (
   peopleid SERIAL PRIMARY KEY,
   firstname TEXT NOT NULL,
   lastname TEXT NOT NULL
);

CREATE TABLE users (
   username TEXT PRIMARY KEY,
   peopleid INT NOT NULL REFERENCES people ON UPDATE CASCADE ON
DELETE RESTRICT,
  ...
);

CREATE TABLE results (
   peopleid INT NO NULL REFERENCES peopleid ON UPDATE CASCADE ON
DELETE CASCADE,
   eventid INT ...
   score  INT...
);

There are some other tables keyed by peopleid that are normally only
populated by user related peopleids.

The site in question is a sports ranking site.  Typically speaking
most "people" are not "users" are have their information populated
from placement sheets.  Some people will later create an account and
after in real life authentication the records need to be merged -- ie
there will be records from both peopleid that will need should be
adjusted to a single value.

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?

Eric

Re: Advice on merging two primary keys...

From
Richard Huxton
Date:
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.

--
   Richard Huxton
   Archonet Ltd

Re: Advice on merging two primary keys...

From
Stephan Szabo
Date:
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."

Re: Advice on merging two primary keys...

From
Eric D Nielsen
Date:
> 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

Re: Advice on merging two primary keys...

From
Stephan Szabo
Date:
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.