SQL 复合主键：是好是坏

Composite Primary Keys : is it good or bad

提问by JAX

I've been designing a database for an online store system. The question that I've come across by reading some posts in this websiteis that although I can use composite primary keys in the case I'm gonna explain below, is it really a bad practice (according to the posts I read in this respect over stackoveflow, many says it is a bad practice so that's why I'm asking).

我一直在为在线商店系统设计数据库。我在阅读本网站的一些帖子时遇到的问题是，虽然我可以在下面要解释的情况下使用复合主键，但这真的是一种不好的做法（根据我在这方面阅读的帖子）在stackoveflow上，很多人说这是一种不好的做法，所以这就是我问的原因）。

I want to store payments for the orders in a separate table. The reason is that, an order can have many items which are handled in a separate table in the form of many to many relationship. Now, if I don't use composite primary keys for my payment table, I'll lose my unique PaymentID:

我想将订单的付款存储在单独的表中。原因是，一个订单可以有许多项目，这些项目以多对多关系的形式在单独的表中处理。现在，如果我的付款表不使用复合主键，我将丢失我的 unique PaymentID：

[PaymentId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[OrderId] INT NOT NULL PRIMARY KEY --Also a Foreign Key--

Now, if I just remove the Primary Key for the OrderId, I'll lose my one to one relationship here so Many OrderIds can be associated to many PaymentIds, and I don't want this.

现在，如果我只是删除 的主键，那么OrderId在这里我将失去一对一的关系Many OrderIds can be associated to many PaymentIds，而且我不想要这个。

This is why the previously asked questions herehave concluded (mostly) that the composite key is a bad idea. So I want to clarify this for myself; if it is bad, what's the best practice then?

这就是为什么之前在这里提出的问题已经得出结论（大部分）复合键是一个坏主意。所以我想为自己澄清这一点；如果不好，那么最佳做法是什么？

回答by Bill Karwin

There is no conclusion that composite primary keys are bad.

没有得出复合主键不好的结论。

The best practice is to have somecolumn or columns that uniquely identify a row. But in some tables a single column is not enough by itself to uniquely identify a row.

最佳做法是让一些列或几列唯一标识一行。但在某些表中，单列本身不足以唯一标识一行。

SQL (and the relational model) allows a composite primary key. It is a good practice is some cases. Or, another way of looking at it is that it's not a bad practice in all cases.

SQL（和关系模型）允许使用复合主键。在某些情况下，这是一个很好的做法。或者，另一种看待它的方式是，在所有情况下，这都不是坏习惯。

Some people have the opinion that everytable should have an integer column that automatically generates unique values, and that should serve as the primary key. Some people also claim that this primary key column should always be called id. But those are conventions, not necessarily best practices. Conventions have some benefit, because it simplifies certain decisions. But conventions are also restrictive.

有些人认为每个表都应该有一个整数列来自动生成唯一值，并且应该作为主键。有些人还声称应始终调用此主键列id。但这些都是惯例，不一定是最佳实践。约定有一些好处，因为它简化了某些决定。但是约定也是有限制的。

You may have an order with multiple payments because some people purchase on layaway, or else they have multiple sources of payment (two credit cards, for instance), or two different people want to pay for a share of the order (I frequently go to a restaurant with a friend, and we each pay for our own meal, so the staff process half of the order on each of our credit cards).

你可能有多个付款的订单，因为有些人购买分期预付，否则他们必须支付的多个源（两张信用卡，例如），或两个不同的人愿意支付订单的份额（我经常去和朋友一起去一家餐馆，我们每个人都自己付餐费，所以工作人员会处理我们每张信用卡上一半的订单）。

I would design the system you describe as follows:

我会设计你描述的系统如下：

Products  : product_id (PK)

Orders    : order_id (PK)

LineItems : product_id is (FK) to Products
            order_id is (FK) to Orders
            (product_id, order_id) is (PK)

Payments  : order_id (FK)
            payment_id - ordinal for each order_id
            (order_id, payment_id) is (PK)

This is also related to the concept of identifying relationship. If it's definitional that a payment exists only because an order exist, then make the order part of the primary key.

这也与识别关系的概念有关。如果付款仅因为订单存在而存在是定义性的，那么将订单作为主键的一部分。

Note the LineItems table also lacks its own auto-increment, single-column primary key. A many-to-many table is a classic example of a good use of a composite primary key.

请注意 LineItems 表也缺少自己的自动增量单列主键。多对多表是很好地使用复合主键的经典示例。

回答by Gordon Linoff

This question is dangerously close to asking for opinions, which can generate religious wars. As someone who is highly biased toward having auto-increasing integer primary keys in my tables (called something like TablenameId, not Id), there is one situation where it is optional.

这个问题非常接近于征求意见，这可能会引发宗教War。作为一个非常倾向于在我的表中自动增加整数主键（称为TablenameId，not 之类的东西Id）的人，在一种情况下它是可选的。

I think the other answers address why you want primary keys.

我认为其他答案解决了您想要主键的原因。

One very important reason is for reference purposes. In a relational database, any entity could -- in theory -- be referenced by another entity via foreign key relationships. For foreign keys, you definitely want one column to uniquely define a row. Otherwise, you have to deal with multiple columns in different tables that align with each other. This is possible, but cumbersome.

一个非常重要的原因是供参考。在关系数据库中，理论上任何实体都可以通过外键关系被另一个实体引用。对于外键，您肯定希望一列唯一地定义一行。否则，您必须处理不同表中彼此对齐的多个列。这是可能的，但很麻烦。

The table you are referring to is not an "entity" table it is a "junction" table. It is a relational database construct for handling many-to-many relationships. Because it doesn't really represent an entity, it should not have foreign key relationships. Hence, a composite primary key is reasonable. There are some situations, such as when you are concerned about database size, where leaving out an artificial primary key is even desirable.

您所指的表不是“实体”表，而是“连接”表。它是一种用于处理多对多关系的关系数据库构造。因为它并不真正代表一个实体，所以它不应该有外键关系。因此，复合主键是合理的。在某些情况下，例如当您担心数据库大小时，甚至需要省略人工主键。

回答by JeromeE

Disk space is cheap, so a primary key clustered on an int identity(1,1) named after a convention (like pk + table name) is a good practice. It will make queries, joins, indexes and other constraints easy to manage.

磁盘空间很便宜，因此主键聚集在以约定命名的 int identity(1,1) 上（如 pk + 表名）是一个很好的做法。它将使查询、连接、索引和其他约束易于管理。

However there's one good reason to no do that (in MS SQL Server at least): if you want to manage the physical sorting of your data in the underlying storage system.

然而，有一个很好的理由不这样做（至少在 MS SQL Server 中）：如果您想管理底层存储系统中数据的物理排序。

The primary key clustered determines the physical sorting order. If you do it on an identity column, the physical sorting order is basically the insert order. However, this may not be the best, especially if you always query the table the same way. On very large tables, getting the right physical sorting order makes queries a lot faster. For example you may want the clustered index on a composite of two columns.

聚集的主键决定了物理排序顺序。如果在标识列上执行，则物理排序顺序基本上是插入顺序。但是，这可能不是最好的，特别是如果您总是以相同的方式查询表。在非常大的表上，获得正确的物理排序顺序可以使查询速度更快。例如，您可能希望聚集索引位于两列的组合上。

回答by Emanuel

Best practices are helpful at best, but blinding at worst. Going against a best practice isn't a sin. Just be sure you know what kind of trade-off you are making.

最佳实践充其量是有帮助的，但最糟糕的是盲目。违背最佳实践并不是一种罪过。只要确保你知道你正在做什么样的权衡。

Database engines can be very complicated things. Without knowing what particular optimizations are made by a given engine, it will be difficult to determine what kinds of constructs will yield the best performance (because I assume that the issue we are talking about here is performance). Composite keys may be problematic for large tables in one kind of database, but not have any noticeable impact for another.

数据库引擎可以是非常复杂的东西。在不知道给定引擎进行了哪些特定优化的情况下，很难确定哪种构造将产生最佳性能（因为我假设我们在这里讨论的问题是性能）。复合键对于一种数据库中的大表可能有问题，但对另一种数据库没有任何明显影响。

A useful practice I've learned is to always strive for having my applications as simple as possible. Do using composite keys save you from having to perform lookups before insertions, or some other nuisance? Use them. If you, however, notice that using them makes your application no longer satisfy some significant performance requirement, consider a solution without them.

我学到的一个有用的做法是始终努力使我的应用程序尽可能简单。使用复合键是否可以避免在插入之前执行查找或其他一些麻烦？使用它们。但是，如果您注意到使用它们会使您的应用程序不再满足某些重要的性能要求，请考虑不使用它们的解决方案。