何时应使用复合主键？

Question

ETA: 我的问题基于保持最佳数据库。在ProjectUserBooleanAttribute中，完全组合主键和使用自动递增PK但然后为PUAT_Id和UserID两者都创建索引的非组合表之间，在数据库性能/大小方面有何区别？进一步阅读后，似乎如果我采用非组合方法，我将不得不在这两个列上创建唯一索引。那么，我仍然需要在这两个列上创建索引吗？如果是这样，这是否意味着该表中的每个列实际上都将具有自己的索引？

这是数据库大小（索引）与性能的典型困境吗？

所以我想创建以下实体：

项目

用户

ProjectUserAttributeTypes

在这个简化的例子中，我的所有ProjectUserAttributeTypes都将是布尔值，因此我只显示ProjectUserBooleanAttribute表。

假设我想要创建两个布尔ProjectUserAttributeType，称为Silver和Gold。我只需在ProjectUserAttributeTypes中创建两行。现在，如果我想将一个用户分配为具有该属性，我将在ProjectUserBooleanAttribute中添加一行。

DBA警告我不要使用组合主键来提高性能。但是，在这种情况下，我看不出不使用组合主键会带来什么好处。在两种情况下，我都需要确保ProjectUserBooleanAttribute对所有列具有非空和唯一值。我肯定也需要索引。

注意：我的最终目标是能够查询我的数据库并找到具有某些属性组合的所有用户。我将连接表以通过项目进行过滤，然后使用where子句进一步过滤。以下是一些示例：

(GOLD OR SILVER)

(GOLD XOR SILVER)

((GOLD OR SILVER) AND NOT (BRONZE))

组合PK

非组合PK

- WhiskerBiscuit

---

可能是复合主键的重复问题。 - Walter Mitty

那么，如果我在（PUAT_ID，UserID）上添加了唯一索引，这会防止像（1,2,3）和（2,2,3）这样的2行吗？如果我需要快速搜索这两列，我是否还需要在这些列上创建单个索引，或者这对于唯一索引来说是多余的？ - WhiskerBiscuit

1. 添加唯一/键（PUAT_Id，UserId）可以防止出现两行相同的情况，因为它们都有一个子行（2,3）。您应该添加这样的唯一/键约束来避免表值中出现这样的对。
2. 如果您的意思是需要针对每个列进行快速搜索，则对于典型的DBMS，复合索引的第一列上的索引将是多余的。请注意，单列索引不是唯一索引。

- philipxy

Answer 1

关于关系型数据库的设计，一般有两种主要方式：

• 自然键
• ID

使用自然键时，你使用给定的键：一个项目通过其项目编号来识别，一个用户通过其登录名或编号来识别，等等。这通常会导致复合（或组合）键：

• project (project_no, name, ...)
• user (user_name, first_name, last_mname, ...)
• project_user (project_no, user_name, role, ...)

表project_user具有复合键：项目编号加上用户名唯一地标识记录，告诉我们谁在哪个项目上工作。

使用ID时，通常会添加一个仅用于链接记录且对用户没有意义的技术ID：

• project (project_id, project_no, name, ...)
• user (user_id, user_name, first_name, last_mname, ...)
• project_user (project_user_id, project_id, user_id, role, ...)

表包含相同的字段以及ID，并且需要与自然键相同的唯一和非空约束以及关于ID的约束。

当然，在project_user中，只有在需要引用该参考的任何表格时才需要project_user_id。但通常会给每个表格都分配一个ID，无论是否需要，只是为了使它们看起来都一样（并且这些ID在以后可能会用到）。

乍一看，基于ID的数据库似乎只是更多的工作和索引，并没有什么收益，但情况并非如此。经常选择ID概念，因为它提供了更多的自由。例如：如果项目编号可以更改会发生什么？使用自然键，项目编号存在于许多表格中，并且必须以某种级联方式进行更新，这可能变得非常繁琐。在ID数据库中，您只需在一个位置更改项目编号即可。

如果项目编号只在公司内部唯一，那会发生什么？在基于ID的数据库中，您需要将company_id添加到projects表中，并在company_id和project_no上添加唯一索引即可完成。使用自然键，则需要在主键中添加公司编号（ILN?人造编号？），并且必须在所有子表中引入该编号。因此：当使用自然键设计数据库时，必须仔细考虑以获得稳定的自然键-有时没有，那么您就必须发明一些。对于ID，您不太关心字段是否可以更改。因此，基于ID的数据库更容易实现。

然后是层次结构。假设数据库中有几个公司，每个公司都有自己的物品和仓库。

自然键：

- 公司（company_code，name，...） - 商品（company_code，item_no，name，...） - 仓库（company_code，warehouse_no，address，...） - 库存（company_code，warehouse_no，item_no，amount，...）

IDs:

- 公司（company_id，name，...） - 商品（item_id，item_no，name，company_id，...） - 仓库（warehouse_id，address，company_id，...） - 库存（stock_id，warehouse_id，item_id，amount，...）使用ID概念，您不需要在股票表中再次命名company_id，因为它是从父表已知的。将其存储在那里甚至是多余的，而在自然键概念中是必需的，因为它是复合键的一部分，没有它我们将失去与其父表的链接。有些人认为这种纯净性是ID概念相对于自然键的巨大优势。但是，它也会带来一个缺点。在自然键数据库中，保证公司物品在公司的仓库中，因为公司是库存表的关键部分。使用ID概念，链接的仓库记录可能属于公司1，而链接的物品记录可能属于公司2。由于某些错误的插入语句引起的不一致数据，DBMS无法防止我们。使用自然键就不会出现此类错误。

如果我想知道一家公司有多少库存，我只需使用自然键从库存选择即可。但是，在ID数据库中，我需要从库存加上另一个表来获取公司。

当数据库基于ID时，你可能会遇到涉及许多更多表的查询。到目前为止，我还没有看到ID数据库比自然键数据库表现更好。但是，我确实看到自然键数据库远远优于ID数据库。这可能是因为我主要看到的是具有许多层次的大型数据库。

关于您的数据库：假设项目ID和用户ID仅是技术内部编号，则它似乎是ID基础数据库-否则，您的数据库将是混合概念（自然项目号，自然用户ID，ProjectUserBooleanAttribute的技术ID）。因此，您的问题实际上与是否使用复合键无关。

PUAT_ID和UserID都必须在ProjectUserBooleanAttribute中，它们不为空，并且您应该对它们进行唯一约束（唯一索引）。因此，它们具有所有主键所需的特性，无论您是否称其为“主键”。添加技术ID仅仅是为了外观而已。它并没有真正改变任何东西。概念保持不变。

在自然键概念中，您会将字段作为主键。但是，您将没有PUAT_Id，而是某个复合键（ProjectId加上AttributeType？）。

在技术ID概念中，您不会将其作为主键，而是使字段非空并添加唯一约束（这使其成为关键字，只是不称为"primary"）。然后，要么添加技术ID作为主键，要么将表格设置为没有ID，因此没有主键。这无关紧要。如果有人要求关键字，请给他们ID，如果不需要，则可以不使用它。只要其他表不需要它，它就是多余的。

- Thorsten Kettner

---

我认为第一个例子不好。我会使用*project (project_id, project_no, ..)，user(user_id, first_name, ..)，project_user(project_id, user_id, role, ..)*。你为什么要使用任何可能会改变的东西作为主键呢？ - Stefan Falk

@displayname：你误解了。我使用项目编号和用户名作为两个示例，用于唯一标识不会更改的内容。必须有某些东西在现实世界中唯一地标识一个项目，而不仅仅是在您的数据库中。在我的示例中，这是一个项目编号。项目4013永远不会被重命名为项目5532。为什么要这样做呢？它是项目的ID，通过它可以在计算机上识别其文档、图表等，而且还可以在真正的纸张、文件夹和抽屉中进行识别 :-) - Thorsten Kettner

@displayname：您正在展示另一个设计：技术ID，您还将其用于复合键。这相当不寻常，出于某种原因。如果您添加了另外两个表project_details和project_user_details，那么您的PK将是什么？project_details(project_details_id)和project_user_details(project_id，user_id，project_details_id)？或者您是否更喜欢project_user_details(user_id，project_details_id)，从而避免冗余，但无法在外键约束中引用project_user表？ - Thorsten Kettner

@displayname: 对于技术ID最好使用非复合主键。如前所述，在项目用户表中，您将拥有项目ID、用户ID的一对独特且不可为空的组合 - 就像一个主键一样。您可以立即将project_user_id添加为表的主键，也可以选择稍后再添加。当您想要创建子表时，最迟需要添加它到project_user表中。 - Thorsten Kettner

Answer 2

当您向表中添加id列时，管理该表的开销不可避免地增加了。但好处是其他表现在可以通过单个id列引用该表的行，而不是旧的复合键。这可以使存储和索引变小，并且可以使那些表的访问更快。此外，它可以使对应实体的引用更加简洁（只有一个列）和更加明显（根据id类型）。请注意，在现在使用该添加的id作为FK的表中，如果您保留任何旧的复合FK列以及id，则应该有一个约束，即这些列的值与id引用的行中这些列的值相同。

- philipxy