database - 何时使用一个字段而不是 2 个字段作为主键？

Question

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

案例一:

用户表

--id[自增]

--用户名

--密码

--邮箱

案例二:

用户表

--用户名

--密码

--邮箱

角色表:

--角色ID

--角色名

用户表角色:

--id[自动增加]

--用户名

--角色ID

我有以下问题:

案例 1:为什么不使用UserName 字段作为主键(PK)？为什么使用另一个字段，例如 id [自动增加] 作为 PK？如果只有UserName 和Email，为什么不使用Email 作为PK？那么，最好的方法是什么？

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

Answer 1

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 是“代理键”。

如果你的模型中有一个自然键，你不能用代理键消除它，你只能替换它。所以问题是:您是只使用自然键，还是使用自然的和代理键？这两种策略实际上都是有效的，并且各有利弊。

代理键的典型原因:

• 使子表中的 FK 更细(在本例中为整数与字符串)，以实现更小的存储空间和更好的缓存。
• 避免需要 ON UPDATE CASCADE。
• 对 ORM 工具的友好程度。

另一方面:

• 您现在有两个键，而不是一个，需要一个额外的索引，使父表 更大且缓存不友好，并且由于索引维护而减慢 INSERT/UPDATE//DELETE。< sup>1
• 可能需要更多的 JOIN-ing²。
• 并且可能无法与clustering 配合使用.³

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

设计者可能希望避免在用户更改电子邮件时必须进行级联更新。

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

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

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

---

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

²您无法仅通过读取联结表找到 UserName - 您必须使用 UserTable 加入它。

³ 代理通常以对客户端应用程序没有意义的方式排序。自动递增代理键的顺序取决于 INSERT 的顺序，并且通常不会对“按插入顺序的用户范围”进行查询。 GUID 等一些代理项可能或多或少是随机排序的。