复合主键和对自然/代理键使用的影响 [英] Composite primary keys and influence on natural/surrogate keys usage

问题描述

我有一个相当简单的问题关于自然/代理键使用在一个明确定义的上下文中经常出现，并且我要说明。

I have a fairly simple question about natural/surrogate key usage in a well-defined context wich manifests itself often, and that i'm going to illustrate.

让我们假设您正在使用SQL Server 2005作为DBMS设计产品的DB模式。
为了简单起见，我们假设只有两个实体被映射到2个表，即Master和Slave。
假设：

Let's assume you are designing the DB schema for a product using SQL Server 2005 as DBMS. For the sake of simplicity let's say there are only two entities involved, wich have been mapped to 2 tables, Master and Slave. Assume that:

1. 我们可以为主单行设置 0..n ;


2. 主表中的列设置（A，B，C，D）是主键的唯一候选;


D是混合的varchar，decimal和bigint列。

1. We can have 0..n Slave entries for a single Master's row;
2. Column set (A, B, C, D) in Master is the only candidate for primary key;
3. Column B in Master is subject to changes over time;
4. A, B, C, D are a mix of varchar, decimal and bigint columns.

问题是：键/约束/引用这些表？
您希望（参数选择）：

The question is: how would you design keys/constraints/references for those tables? Would you rather (argumenting your choice):

1. 实施复合 在Master上（A，B，C，D）和相关的复合外键在Slave上，或


2. 引入代理键 K （A，B，C，D）上添加一个UNIQUE约束，或者在主机（A，B，C，D）上添加UNIQUE约束， / li>
   
3. 使用不同的方法。

1. Implement a composite natural key on Master on (A, B, C, D), and a related composite foreign key on Slave, or
2. Introduce a surrogate key K on Master, let say an IDENTITY(1,1) column with a related (single column) foreign key on Slave, adding a UNIQUE constraint on Master's (A, B, C, D), or
3. Use a different approach.

主要是因为假设3）和性能方面的，但我想听听别人的意见（因为有一个关于这个话题的公开辩论）。

As for me I'd go with option 2), mainly because of assumption 3) and performance-wise, but I'd like to hear someone else's opinion (since there is quite an open debate on the topic).

谢谢您

推荐答案

1,2或3.没有足够的信息来确定代理是必需的还是有用的有可能。任何复合键属性也是从表中某些键或约束的一部分？是否有一些其他的主键可以用作外键？关键值可能改变的事实不应该是决定性因素，因为任何关键值都可能需要改变 - 代理也不例外。

Either 1,2 or 3. There isn't necessarily enough information to determine whether a surrogate is necessary or how useful it might be. Are any of the compound key attributes also part of some key or constraint in the Slave table? Is there some other key of Master that could be used as a foreign key? The fact that a key value may change shouldn't be the deciding factor because any key value may need to change - surrogates are no exception.

有关
主题的相当开放的辩论

there is quite an open debate on the topic

不幸的是，大部分辩论是基于错误的假设您需要在代理或自然键之间进行选择。因为你的选择2正确地建议你可以使用两者作为需要出现。一个不能替代另一个，因为不同属性上的简单键和复合键在数据模型中显然意味着不同的东西，并对数据执行不同的约束。

Unfortunately, much of that debate is based on the mistaken assumption that you need to choose between either a surrogate or a natural key. As your option 2 rightly suggests you can use both as the need arises. One is not a substitute for the other because simple keys and compound keys on different attributes obviously mean different things in your data model and enforce different constraints on your data.

这篇关于复合主键和对自然/代理键使用的影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！