Geek Answers Handbook

Primary Key Merge - Update Cascades

Is there a way to combine the two primary keys into one, and then cascade to update all the affected relationships? Here's the script:

Customers (idCustomer int PK, 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 combined into one. For example, you have a client with identifier 1, and the other with identifier 2. You want to combine both, so that everything that was 2 is now 1. I know I can write a script that updates all the affected tables one by one, but I would like to make this a more promising proof using cascade rules, so I don't need to update the script every time a new relationship is added.

Any ideas?

+4
source share
4 answers

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

You can also just wrap the entire operation in a transaction (in any case, this is a good idea). The last step will be to remove the client from which you merged, so if the table you never added and you are trying to merge does not happen because you cannot delete the client merged from it, because there are dependent records in a table that has not yet been added to the merge procedure.

+3
source

Use Triggers instead. When updating Clients (idCustomer column), you make any necessary changes (Delete, Update ...) in the related tables.

0
source

It looks like you will have two records for each of these tables that have the same primary key. Is that your intention?

  Customers
     idCustomer Company
          1 AT&T
          2 cingular

After the merger will become

  Customers
     idCustomer Company
          1 AT&T
          1 Cingular

In any case, the most promising solution would be to write a procedure. You can go through the procedure for both customers and for all the work to update all the tables as necessary.

0
source

How is a more recent solution possible?

I have the same problem, and at the moment, creating procedures dynamically seems too complicated. Here, how could this work in theory, but I think it is not?

In one transaction: 1) Temporarily disable the primary key restriction for clients 2) Update the Cingular primary identifier to '1', which has a cascading relationship update rule taking care of children 3) Use the secondary key field to delete (only) Cingular 4) Enable the restriction primary keys for customers

We expect something similar in the future T-SQL:

REMOVE WITH UPDATE idCustomer = 1 FROM WHERE clients idCustomer = 2;

; -)

0
source

More articles:


All Articles