何时使用一个字段作为主键而不是2？ [英] When to use one field as primary key instead of 2?

问题描述

我经常看到这样的数据库设计：

案例1：

UserTable

- id [auto increase]

- UserName

- 密码

- 电子邮件

案例2：

UserTable

- UserName

- 密码

- 电子邮件

RoleTable： p>

- RoleID

- RoleName

strong> UserTableRole：

- id [auto increase]

p>

- RoleID

我有以下问题：

在案例1中：
为什么不使用 UserName 字段作为主键（ PK ）？为什么要使用另一个像 ID （这是自动增加的）作为PK？
如果只是 UserName 和电子邮件，为什么不使用电子邮件 PK ？

在情况2：
在UserRoleTable中，为什么不使用 UserName 和 RoleID 作为PK？为什么使用 ID 的另一个文件夹[自动增加]为 PK ？
那么，在这种情况下，最好的方法是什么？

解决方案

在案例1中：为什么不使用UserName字段作为主键？为什么使用另一个喜欢的id [这是自动增加]作为PK？

UserTable.UserName 在此数据模型中具有内在意义，并称为自然键。另一方面， UserTable.id 是代理键。

如果你的模型中有一个自然键，你不能用代理键消除它，你可以只是取代它。所以问题是：你只是使用自然键，或自然的和代理键？

代理键的典型原因：

• 为了在更小的存储空间和更好的缓存中保持FKs 子表更小（在这种情况下为整数与字符串）。


• 避免需要对于ON UPDATE CASCADE。


• 对ORM工具的友好性。

/ p>

• 现在，您有两个键，而不是一个键，需要额外的索引，使父表 ¹


• 可能需要更多的JOIN-ing ²


• 可能无法在群集。 ³

只有UserName和电子邮件的情况下，为什么不使用电子邮件作为PK？

设计者可能想避免ON CASCADE UPDATE

在情况2：在UserRoleTable中，为什么不使用UserName和RoleID作为PK？ p>

如果同一用户/角色对不能有多个连接，那么在任何情况下都必须有一个键。

除非有FK引用 UserTableRole 的子表，或使用不友好的ORM，没有理由增加一个替代PK

---

¹ 如果使用聚类，键可能是额外的fat（因为它包含聚集键的副本，通常是PK），并且在查询时需要双查找（因为聚簇表中的行没有稳定的物理位置，因此必须位于

² 例如，一个集群密钥，禁止某些DBMS特定的优化，例如Oracle的rowid猜测您将无法通过读取连接表找到 UserName - 您必须使用 UserTable 。

³ 代理通常以对客户端应用程序无意义的方式排序。自动递增代理键的顺序取决于INSERT的顺序，并且通常不会在通过其插入顺序的用户范围上进行查询。某些替代品（例如GUID）可能会随机排序。

I often see some database design like this:

Case 1:

UserTable

--id[auto increase]

--UserName

--Password

--Email

Case 2:

UserTable

--UserName

--Password

--Email

RoleTable:

--RoleID

--RoleName

UserTableRole:

--id[auto increased]

--Username

--RoleID

I have questions as follows:

In Case 1: Why not use UserName field as primary key (PK)? why use another filed likes id [which is auto increased] as PK? In case of just UserName and Email, why not use Email as PK? So, What is the best approach?

In Case 2: In the UserRoleTable, why not use both UserName and RoleID as PK? why use another filed likes id [which is auto increased] as PK? So, What is the best approach in this case?

解决方案

In Case 1: Why not use UserName field as primary key (PK)? why use another filed likes id [which is auto increased] as PK?

The UserTable.UserName has intrinsic meaning in this data model and is called "natural key". The UserTable.id, on the other hand, is "surrogate key".

If there is a natural key in your model, you cannot eliminate it with the surrogate key, you can just supplant it. So the question is: do you just use the natural key, or the natural and surrogate key? Both strategies are actually valid and have their pros and cons.

Typical reasons for surrogate key:

• To keep FKs in child tables slimmer (integer vs. string in this case), for smaller storage and better caching.
• Avoid the need for ON UPDATE CASCADE.
• Friendliness toward ORM tools.

On the other hand:

• You now have two keys instead of one, requiring an extra index, making the parent table larger and less cache-friendly, and slowing down INSERT/UPDATE//DELETE due to index maintenance.¹
• May require more JOIN-ing².
• And may not play well with clustering.³

In case of just UserName and Email, why not use Email as PK?

The designer probably wanted to avoid ON CASCADE UPDATE that would be necessary if user changed the e-mail.

In Case 2: In the UserRoleTable, why not use both UserName and RoleID as PK?

If there cannot be multiple connections for the same user/role pair, you have to have a key on that in any case.

Unless there are child tables with FKs referencing UserTableRole or an unfriendly ORM is used, there is no reason for an additional surrogate PK.

---

¹ And if clustering is used, the secondary index under the natural key may be extra "fat" (since it contains a copy of the clustering key, which is typically PK) and require a double-lookup when querying (since rows in clustered table don't have stable physical locations, so must be located through a clustering key, barring some DBMS-specific optimizations such as Oracle's "rowid guesses").

² E.g. you wouldn't be able to find UserName just by reading the junction table - you'd have to JOIN it with the UserTable.

³ Surrogates are typically ordered in a way that is not meaningful to the client applications. The auto-increment surrogate key's order depends on the order of INSERTs, and querying is not typically done on a "range of users by their order of insertion". Some surrogates such as GUIDs may be more-less randomly ordered.

这篇关于何时使用一个字段作为主键而不是2？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！