合并数据库如何处理重复的PK [英] Merging databases how to handle duplicate PK's

问题描述

我们有三个物理地区分开的数据库，一个在LA，SF和NY。所有数据库共享相同的模式，但包含特定于其区域的数据。我们希望将这些数据库合并成一个镜像。我们需要保留每个区域的数据，但将它们合并到一个数据库中。这对我们来说有很多问题，例如我们肯定有重复的主键，外键可能是无效的。

We have three databases that are physically separated by region, one in LA, SF and NY. All the databases share the same schema but contain data specific to their region. We're looking to merge these databases into one and mirror it. We need to preserve the data for each region but merge them into one db. This presents quite a few issues for us, for example we will certainly have duplicate Primary Keys, and Foreign Keys will be potentially invalid.

我希望找到一个人已经有一个像这样的任务的经验可以提供一些提示，战略和经验的话如何我们可以完成合并。

I'm hoping to find someone who has had experience with a task like this who could provide some tips, strategies and words of experience on how we can accomplish the merge.

例如，一个想法是创建复合键，然后改变我们的代码和sprocs通过复合键（region / original pk）找到数据。但这需要我们更改所有的代码和sprocs。

For example, one idea was to create composite keys and then change our code and sprocs to find the data via the composite key (region/original pk). But this requires us to change all of our code and sprocs.

另一个想法是只导入数据，让它生成新的PK，然后更新所有的FK引用新的PK。

Another idea was to just import the data and let it generate new PK's and then update all the FK references to the new PK. This way we potentially don't have to change any code.

欢迎任何经验！

推荐答案

我没有这方面的第一手经验，但在我看来，你似乎应该能够为每个服务器唯一地映射PK - >新PK。例如，生成新的PK，使得来自LA服务器的数据具有PK％3 == 2，SF具有PK％3 == 1，并且NY具有PK％3 == 0。并且自从我理解你的问题，服务器只将FK关系存储到其自己的数据，您可以以相同的方式更新FK。

I have no first-hand experience with this, but it seems to me like you ought to be able to uniquely map PK -> New PK for each server. For instance, generate new PKs such that data from LA server has PK % 3 == 2, SF has PK % 3 == 1, and NY has PK % 3 == 0. And since, as I understood your question anyway, each server only stores FK relationships to its own data, you can update the FKs in identical fashion.

NewLA = OldLA*3-1
NewSF = OldLA*3-2
NewNY = OldLA*3

然后可以合并那些并且没有重复的PK。这实质上是，如你所说，只是生成新的PK，但是这种方式的结构化允许你轻轻地更新你的FK（假设，因为每个服务器上的数据是孤立的）。祝你好运。

You can then merge those and have no duplicate PKs. This is essentially, as you already said, just generating new PKs, but structuring it this way allows you to trivially update your FKs (assuming, as I did, that the data on each server is isolated). Good luck.

这篇关于合并数据库如何处理重复的PK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！