Merge Primary Keys - Cascade Update

Question

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?

Answer 1

Consider using Triggers instead. On update of the Customers (idCustomer column), you do whatever needed modifications (Delete, Update ...) on the related tables.

Answer 2

There's no automatic way to do it, but you have a couple options, you can manually write the procedures, or you can either code generate the merge on a regular basis or dynamically generate it at run-time. To do this, you can use the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE and INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES to build the procedure dynamically.

You can also simply wrap the entire operation in a transaction (a good idea anyway). The last step will be to remove the customer being merged out of, so if there is RI on a table you never added and you try to do a merge, it will fail because you cannot remove the customer being merged out of since there are dependent records in a table which wasn't already added to the merge procedure.

Answer 3

Any more recent solution to this?

I have kind of the same problem and at the moment building the procedures dynamically seems too complicated. Here's how it could work in theory, but I guess it doesn't?

In one transaction: 1) Temporarily disable primary key constraint on Customers 2) Update Primary ID of Cingular to '1' which has a relationship update cascade rule taking care of the children 3) Use a secondary key field to delete (only) Cingular 4) Enable primary key constraint on Customers

Looking forward to something like this in future T-SQL:

DELETE WITH UPDATE idCustomer = 1 FROM Customers WHERE idCustomer = 2;

;-)