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
