Merge Primary Keys - Cascade Update
- by Chris Jackson
Is there a way to merge two primary keys into one and then cascade update all affected relationships? Here's the scenario:
Customers (idCustomer int PK, Company varchar(50), etc)
CustomerContacts (idCustomerContact int PK, idCustomer int FK, Name varchar(50), etc)
CustomerNotes (idCustomerNote int PK, idCustomer int FK, Note Text, etc)
Sometimes customers need to be merged into one. For example, you have a customer with the id of 1 and another with the id of 2. You want to merge both, so that everything that was 2 is now 1. I know I could write a script that updates all affected tables one by one, but I'd like to make it more future proof by using the cascade rules, so I don't have to update the script every time there is a new relationship added.
Any ideas?