0

I'm trying to de-duplicate user accounts in our system and I know there are lots of questions out there about removing/identifying duplicates (such as Remove duplicate rows in MySQL), but I haven't seen any that required maintaining referential records.

I have a users table and a subscriptions table with a foreign key field User_ID common to both and set to CASCADE in subscriptions.

I'd like to remove all duplicates in the users table but in doing so, all of the records corresponding to User_ID in the subscriptions table would be lost due to the CASCADE behavior.

Is it possible to UPDATE the users table, altering the User_ID of the duplicate records to the one I want to keep, without colliding with the unique index, allowing all referential records to be updated accordingly and finally removing the duplicate User record without cascading the delete?

The added complication is that the User_ID field in the users table is obviously indexed with unique.

EDIT: I should add that this is a simplified example, our DB has 100+ tables many of which have foreign keys based on the User_ID.

Community
  • 1
  • 1
oucil
  • 4,211
  • 2
  • 37
  • 53
  • take the list of dupe users, pick the users that are "kept". take that kept list, and update the subscriptions table so that the dupeuser->sub is changed to keptuser->sub. then you can delete the dupe users because they no longer have any "child" subscriptions. – Marc B Nov 29 '13 at 16:16
  • @MarcB I might have been putting up the edit when you chimed in, so you might have missed this, it's a little more complicated being as there are potentially over 100 tables that would need to manually be updated in that case. That's what I'm trying to avoid if at all possible. – oucil Nov 29 '13 at 16:18
  • remove the unique index, then? if only while doing the fixups. strip index, update users table and allow the cascades to do their magic, delete the now-duplicate user IDs, reapply unique index. – Marc B Nov 29 '13 at 16:19
  • @MarcB You can't do that, the unique indexes are required by the foreign key constraints for compatibility. – oucil Nov 29 '13 at 16:55
  • 1
    then you're hosed. you'll have to manually update each of your child tables. – Marc B Nov 29 '13 at 16:57
  • @MarcB Must be a fellow canuck ;) Thanks for your thoughts, was hoping for a silver bullet. I'll leave it open a little just in case there are any crazies out there. – oucil Nov 29 '13 at 21:07

1 Answers1

0

So in the end, as @MarcB helped me discover above, the correct answer is to have planned better in the beginning ;)

So in the end, we're going to have to write a programatic solution to manually join accounts. We're lucky enough to have DAO/DTO's for every object type and so it shouldn't be too bad dealing with the referential records, it'll just be an intense operation and so will require some good planning wink.

oucil
  • 4,211
  • 2
  • 37
  • 53