Advice on merging two primary keys...

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Advice on merging two primary keys...

nielsene-2
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Advice on merging two primary keys...

Richard Huxton
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: Advice on merging two primary keys...

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: Advice on merging two primary keys...

nielsene-2
In reply to this post by nielsene-2
> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: Advice on merging two primary keys...

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]