Hi there,
I have referential integrity set up on a relational database in mysql 4xx. It's a beautiful thing, esp. the cascade delete when testing record entry.
One of the problems I anticipate facing is duplicate records. The username (example sfullman or jsmith) is hte primary key in the master table. The email must also be unique (no two people can have the same email).
What we want to do is allow people to make purchases, sign up for the site, etc., without bugging them that their email is already present. So, if their email is in the sytem, we generate a unique user name, add a ! to the front of their email, and insert them as a new record.
We then create an interface to compare me@mysite.com with !me@mysite.com. If they are in fact the same person, we want to merge the new record into the existing record.
The problem is, how to I do that with the dependent records? Suppose I have the original username = jsmith, and the generated username = jsmith02. I decide they're the same person. I can't just delete jsmith02, it would cascade delete his purchases. I also can't update the foreign key jsmith02 to jsmith in the dependent purchase tables -- that's not allowed.
Has anyone got any advice on this issue of merging two records into one when there's dependencies involved?
Thank you very much,
Sam Fullman
