复合主键:好还是坏? [英] Composite Primary Keys : Good or Bad?

问题描述

虽然可以使用复合主键，但对于下面的情况，这真的是一种不好的做法吗?关于 Stackoveflow 的共识在这个问题上似乎是双向的.

Although one can use composite primary keys, for the case below, is it really a bad practice? The consensus on Stackoveflow seems to go both ways on this issue.

为什么?

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

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:

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

现在，如果我只是删除 OrderId 的主键，我将在这里失去一对一的关系，因此 许多 OrderId 可以关联到许多 PaymentId，我不想要这个.

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.

这似乎就是为什么其他关于 SO 的答案都得出结论(主要是)复合键是一个坏主意.如果不好，那么最佳做法是什么?

This seems to be why other answers on SO have concluded (mostly) that the composite key is a bad idea. If it is bad, what's the best practice then?

推荐答案

没有结论说复合主键不好.

There is no conclusion that composite primary keys are bad.

最佳实践是让一些列或多列唯一地标识一行.但在某些表中，单列本身不足以唯一标识一行.

The best practice is to have some column 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(和关系模型)允许复合主键.在某些情况下，这是一个很好的做法.或者，另一种看待它的方式是，这在所有情况下都不是坏习惯.

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.

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

Some people have the opinion that every table 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.

您的订单可能有多次付款，因为有些人分期付款，或者他们有多种付款方式(例如两张信用卡)，或者两个不同的人想要支付一份订单的费用(我经常和朋友一起去餐馆，我们每个人都自己付饭钱，所以工作人员处理了一半的订单在我们的每张信用卡上).

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.

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

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.

这篇关于复合主键:好还是坏?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！