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

问题描述

我经常看到一些这样的数据库设计:

I often see some database design like this:

案例 1:

用户表

--id[自动增加]

--用户名

--密码

--电子邮件

情况 2:

用户表

--用户名

--密码

--电子邮件

角色表:

--RoleID

--角色名称

用户表角色:

--id[自动增加]

--用户名

--RoleID

我有以下问题:

在情况 1 中:为什么不使用 UserName 字段作为主键 (PK)?为什么使用另一个字段喜欢 id [这是自动增加的] 作为 PK?如果只有用户名和电子邮件，为什么不使用电子邮件作为PK?那么，最好的方法是什么?

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?

在情况 2 中:在 UserRoleTable 中，为什么不同时使用 UserName 和 RoleID 作为 PK?为什么使用另一个喜欢的 id [这是自动增加的] 作为 PK?那么，在这种情况下最好的方法是什么?

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?

推荐答案

情况 1:为什么不使用 UserName 字段作为主键 (PK)?为什么使用另一个提交的喜欢 id [这是自动增加的] 作为 PK?

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

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

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.

代理键的典型原因:

• 将 FK 保留在 子表 更精简(在本例中为整数与字符串)，以实现更小的存储和更好的缓存.
• 避免需要 ON UPDATE CASCADE.
• 对 ORM 工具友好.

• 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.

另一方面:

• 您现在有两个键而不是一个，这需要一个额外的索引，从而使父表变得更大且对缓存不友好，并且由于索引维护而减慢了 INSERT/UPDATE//DELETE.¹
• 可能需要更多的 JOIN-ing².
• 并且可能不适用于集群.³

• 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.³

如果只有UserName和Email，为什么不使用Email作为PK呢?

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

设计者可能希望避免在用户更改电子邮件时所必需的 ON CASCADE UPDATE.

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

情况2:在UserRoleTable中，为什么不同时使用UserName和RoleID作为PK?

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.

除非有 FK 引用 UserTableRole 的子表或使用了不友好的 ORM，否则没有理由使用额外的代理 PK.

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

¹ 并且如果使用聚类，自然键下的二级索引可能会额外胖"(因为它包含聚类键的副本，通常是PK)和查询时需要双重查找(因为聚簇表中的行没有稳定的物理位置，因此必须通过聚簇键定位，除非某些特定于 DBMS 的优化，例如 Oracle 的rowid 猜测").

¹ 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").

² 例如仅通过读取联结表，您将无法找到 UserName - 您必须将它与 UserTable 连接起来.

² 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.

³ 代理的排序方式通常对客户端应用程序没有意义.自动递增代理键的顺序取决于 INSERT 的顺序，并且查询通常不在按插入顺序排列的用户范围"上进行.某些代理(例如 GUID)可能会随机排序.

³ 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屋！