合并主键-级联更新 [英] Merge Primary Keys - Cascade Update

问题描述

是否可以将两个主键合并为一个，然后级联更新所有受影响的关系？情况如下：

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

客户（idCustomer int PK，公司varchar（50）等）

Customers (idCustomer int PK, Company varchar(50), etc)

CustomerContacts（ idCustomerContact int PK，idCustomer int FK，名称varchar（50等）

CustomerContacts (idCustomerContact int PK, idCustomer int FK, Name varchar(50), etc)

CustomerNotes（idCustomerNote int PK，idCustomer int FK，注释文本等）

CustomerNotes (idCustomerNote int PK, idCustomer int FK, Note Text, etc)

有时客户需要合并为一个。例如，您有一个ID为1的客户，另一个ID为2的客户。您想将两者合并，因此现在2的所有内容都为1。我知道我可以编写一个脚本来更新所有受影响的表一个，但是我想通过使用级联规则使其更适合将来使用，因此我不必每次添加新关系时都更新脚本。

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.

有什么想法吗？

推荐答案

没有自动方法，但是有两种选择，您可以手动编写这些过程，或者您可以代码定期生成合并，也可以在运行时动态生成合并。为此，您可以使用 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 和 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 和 INFORMATION_SCHEMA。 TABLE_CONSTRAINTS 和 INFORMATION_SCHEMA.COLUMNS和INFORMATION_SCHEMA.TABLES 来动态构建过程。

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.

您还可以简单地将整个操作包装在一个事务中（无论如何是个好主意）。最后一步是从中删除要合并的客户，因此，如果您从未添加过的表上存在RI并尝试进行合并，则它将失败，因为您不能从其中删除要合并的客户，因为表中的记录（尚未添加到合并过程中）。

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.

这篇关于合并主键-级联更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！