复合主键：是好还是坏 [英] Composite Primary Keys : is it good or bad

问题描述

我一直在为在线商店系统设计数据库。 我通过阅读这个网站的一些帖子遇到的问题是，虽然我可以使用复合主键在下面我要解释的情况下，这是一个坏的做法（根据我在这方面阅读的帖子在stackoveflow，很多人说这是一个坏的做法，这就是为什么我问）。

I've been designing a database for an online store system. The question that I've come across by reading some posts in this website is 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).

我想存储订单的付款单独的表。原因是，订单可以具有许多项目，它们也以多对多关系的形式在单独的表中处理。现在如果我不使用复合主键作为我的付款表，我会失去我的独特 PaymantID ：

I wanna store payments for the orders in a separate table. The reason is that, an order can have many items which are handled also 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 PaymantID:

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

现在，如果我只是删除 OrderId 的主键在这里失去我的一对一的关系这样许多OrderIds可以关联到许多PaymentIds ，我不想要这个。

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.

这是之前提出的问题（主要是）认为复合键是一个坏主意。

This is while the previously asked questions here have concluded (mostly) that composite key is a bad idea. So I wanna clarify this to myself, 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)

这也与的概念相关识别关系。

请注意，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屋！