Обсуждение: Advice on merging two primary keys...
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
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
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."
> 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
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.