将两个相同的表与视图合并时如何避免主键冲突
我有一个中央数据库和一个用于我正在从事的项目的独特数据库。中央数据库允许我为用户提供默认选项和数据。
独特的数据库与中央数据库结构相匹配，让用户拥有自己的定制数据。我有将每个相同的表对联合起来的视图。
每个视图中的“主键”字段都设置为我正在使用的 dbml (Linq-to-sql) 中的主键。然后，我将关联添加到 dbml 中的其他表。
这意味着我无法将两个表设置为以 0 为基数自动递增，因为主键在唯一数据库表中用作“外键”（我知道在本例中它们不是严格的外键）。
因此，在视图中，我需要每个表对中的所有记录都具有唯一的主键。
我曾考虑过将唯一数据库 pk 基数设置为 1000000 或其他值，但是当全局数据库（0 基数）赶上时，这最终可能会适得其反。
我还想在视图中为每个前缀添加一个数字，例如
Global: 11, 12, 13, 14, 15, 16, 17, 18, 19, 110 ,111 Unique: 21, 22, 23, 24, 25, 26, 27, 28, 29, 210, 211
我担心这会影响查询时的性能，这必须尽可能高效。
不确定最好的方法？
I have a central database and a unique database for a project I am working on. The central database allows me to provide users with default options and data.
The unique database matches the central database structure to allow users own customized data. I have views that union each identical table pair.
The "primary key" field in each view is set as a primary key in the dbml I am using (Linq-to-sql). I then add associations to other tables in the dbml.
This means that I cannot set both tables to auto-increment with a base of 0, because the primary keys are used as "foreign keys" in unique db tables (I know they're not strictly foreign keys in this instance).
Therefore in the view, I need all records from each table pair to have a unique primary key.
I have thought about setting the unique database pk base number at 1000000 or something, but this eventually may backfire on me when the global database (0 base) caught up.
I also though about prefixing each with a number in the view, e.g.
Global: 11, 12, 13, 14, 15, 16, 17, 18, 19, 110 ,111
Unique: 21, 22, 23, 24, 25, 26, 27, 28, 29, 210, 211
I am worried how this may affect performance when querying, this has to be as efficient as possible.
Not sure of the best method?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的解决方案的一个问题是它很脆弱，如果您需要合并两个以上的源，就会失败。根据您的业务场景，这可能是也可能不是现实的风险。
人们有时通过使用 GUID 作为候选键来解决这个问题。通过这种方式，您的源数据库将 IDENTITY 列作为 PK，并且还有一个 GUID，该 GUID 在源数据库中是唯一的但不是主要的。然而，在合并视图中，PK 是 GUID，原始源密钥 (IDENTITY) 会随带一起使用，但实际上并未在 PK/FK 关系中使用。
在这种类型的模型中，合并视图通常还包括某种源代码列，它告诉您行来自哪里。如果这样做，那么源代码+身份密钥也是合并视图中的候选密钥。
One issue with your solution is that it is brittle and would fail if you ever needed more than two sources to be merged. This may or may not be a realistic risk depending on your business scenario.
People sometimes get around this issue by using a GUID for a candidate key. In this way, your source DBs have the IDENTITY columns as PK and also a GUID which is unique but not primary in the source databases. However, in the merged view the PK is the GUID and the originial source keys (IDENTITY) are brought along for the ride but don't actually get used in PK/FK relationships.
In this type of model, the merged view typically also includes some kind of source code column which tells you where the row came from. If you do this, then source code + identity key is also a candidate key in the merged view.
只是实际上有另一个想法：
我可以将 unique 设置为基数 1 增量 2 和全局基数 2 增量 2。这样，就不会出现疯狂的 hack，并且 pk 永远不会发生冲突
Just actually had another idea:
I could set the unique to base 1 increment 2 and the global base 2 increment 2. This way, there is no crazy hack and the pk's will never clash