I am building a system that collects information about users across different internet services, but doesn't know anything about those users upfront. I store this information in a MySQL database. There are two tables, external users which represents usernames in some service, and internal users, which represents the fact that many external users are in fact the same person.
EXTERNAL_USER(internal_user_id REFERENCES INTERNAL_USER(id) ON UPDATE CASCADE,service_id,name)
INTERNAL_USER(id PRIMARY KEY,...)
OTHER_TABLES_USING_INTERNALUSER(internal_user_id REFERENCES INTERNAL_USER(id) ON UPDATE CASCADE,..)
The trouble is I sometimes wind up with external users that are split across internal users, because I didn't originally know those usernames corresponded to the same user.
e.g.
--What I have
EXT
(1,a,..)
(1,b,..)
(2,c,..)
INT
(1,..)
(2,..)
OTHER
(1,..)
(2,..)
--What I want
EXT
(a,1,..)
(b,1,..)
(c,1,..)
INT
(1,..)
OTHER
(1,..)
(1,..)
The trouble is to to do this I would somehow need to swizzle all the references to INTERNAL(2) to point at INTERNAL(1), since I can't change the PRIMARY KEY of INTERNAL(2) to INTERNAL(1).
One way is to just use my business logic to update the references in each of the parent tables, but that would be tedious and potentially error prone, so I was hoping there is just a way to let the database know that the INTERNAL rows should be merged.