为什么使用多列作为主键（复合主键）
此示例取自来自 w3schools。
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
我的理解是，两列（P_Id 和 LastName）一起代表表 Persons 的主键。这是正确的吗？
- 为什么有人想要使用多个列作为主键而不是单个列？
- 给定表中可以有多少列一起用作主键？
This example is taken from w3schools.
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
My understanding is that both columns together (P_Id and LastName) represent a primary key for the table Persons. Is this correct?
- Why would someone want to use multiple columns as primary keys instead of a single column?
- How many columns can be used together as a primary key in a given table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
你的理解是正确的。
在很多情况下你都会这样做。一个示例是
OrderHeader和OrderDetail之类的关系。OrderHeader中的 PK 可能是OrderNumber。OrderDetail中的 PK 可能是OrderNumberANDLineNumber。如果是这两者中的任何一个，它都不会是唯一的，但两者的组合保证是唯一的。另一种方法是使用生成的（非智能）主键，例如本例中的
OrderDetailId。但这样你就不会总是那么容易地看到这种关系。有些人喜欢一种方式；有些人喜欢一种方式。有些人更喜欢另一种方式。Your understanding is correct.
You would do this in many cases. One example is in a relationship like
OrderHeaderandOrderDetail. The PK inOrderHeadermight beOrderNumber. The PK inOrderDetailmight beOrderNumberANDLineNumber. If it was either of those two, it would not be unique, but the combination of the two is guaranteed unique.The alternative is to use a generated (non-intelligent) primary key, for example in this case
OrderDetailId. But then you would not always see the relationship as easily. Some folks prefer one way; some prefer the other way.复合主键的另一个例子是关联表的使用。假设您有一个包含一组人员的人员表和一个包含一组组的组表。现在您想要在个人和组上创建多对多关系。这意味着每个人可以属于许多群体。以下是使用复合主键的表结构。
Another example of compound primary keys are the usage of Association tables. Suppose you have a person table that contains a set of people and a group table that contains a set of groups. Now you want to create a many to many relationship on person and group. Meaning each person can belong to many groups. Here is what the table structure would look like using a compound primary key.
W3Schools 示例并未说明何时应使用复合主键，而仅提供了使用与其他键相同的示例表的示例语法。
他们选择的示例可能会通过组合无意义的键 (P_Id) 和自然键 (LastName) 来误导您。这种奇怪的主键选择表明，根据架构，以下行是有效的，并且是唯一标识学生所必需的。直觉上这是没有意义的。
进一步阅读： 伟大的主键辩论或只是谷歌
无意义的主键，甚至仔细阅读此SO 问题FWIW - 我的 2 美分是避免多列主键并使用单个生成的 id 字段（代理键）作为主键，并在必要时添加额外的（唯一）约束。
The W3Schools example isn't saying when you should use compound primary keys, and is only giving example syntax using the same example table as for other keys.
Their choice of example is perhaps misleading you by combining a meaningless key (P_Id) and a natural key (LastName). This odd choice of primary key says that the following rows are valid according to the schema and are necessary to uniquely identify a student. Intuitively this doesn't make sense.
Further Reading: The great primary-key debate or just Google
meaningless primary keysor even peruse this SO questionFWIW - My 2 cents is to avoid multi-column primary keys and use a single generated id field (surrogate key) as the primary key and add additional (unique) constraints where necessary.
每当您想要确保多个属性的组合的唯一性时，就可以使用复合键（具有多个属性的键）。单个属性键无法实现相同的效果。
You use a compound key (a key with more than one attribute) whenever you want to ensure the uniqueness of a combination of several attributes. A single attribute key would not achieve the same thing.
是的，它们都构成主键。特别是在没有代理键的表中，可能需要指定多个属性作为每个记录的唯一标识符（不好的例子：同时具有名字和姓氏的表可能要求它们的组合是唯一的）。
Yes, they both form the primary key. Especially in tables where you don't have a surrogate key, it may be necessary to specify multiple attributes as the unique identifier for each record (bad example: a table with both a first name and last name might require the combination of them to be unique).
一般来说，一个键中的多个列的性能比代理键的性能更差。我更喜欢有一个代理键，然后在多列键上有一个唯一索引。这样您就可以获得更好的性能并保持所需的独特性。更好的是，当该键中的某个值发生更改时，您不必更新 215 个子表中的一百万个子条目。
Multiple columns in a key are going to, in general, perform more poorly than a surrogate key. I prefer to have a surrogate key and then a unique index on a multicolumn key. That way you can have better performance and the uniqueness needed is maintained. And even better, when one of the values in that key changes, you don't also have to update a million child entries in 215 child tables.
你的第二个问题
是特定于实现的：它是在实际使用的 DBMS 中定义的。[1],[2],[3] 您必须检查您使用的数据库系统的技术规范。有些非常详细，有些则不是。在网上搜索此类限制可能很困难，因为术语各不相同。术语复合主键应该是强制性的；）
如果您找不到明确的信息，请尝试创建一个测试数据库以确保您可以预期对限制违规的稳定（和具体）处理（这是预期的） ）。请小心获取有关此问题的正确信息：有时限制会累积，并且您会看到不同数据库布局的不同结果。
Your second question
is implementation specific: it's defined in the actual DBMS being used.[1],[2],[3] You have to inspect the technical specification of the database system you use. Some are very detailed, some are not. Searching the web about such limitations can be hard because the terminology varies. The term composite primary key should be mandatory ;)
If you cannot find explicit information, try creating a test database to ensure you can expect stable (and specific) handling of the limit violations (which are to be expected). Be careful to get the right information about this: sometimes the limits are accumulated, and you'll see different results with different database layouts.
当您在关系数据库中使用中间表时，在多个表上使用主键会派上用场。
我将使用我曾经制作的数据库作为示例，特别是该表中的三个表。几年前，我为网络漫画创建了一个数据库。有一个表称为“漫画”，列出了所有漫画、标题、图像文件名等。主键是“comicnum”。
第二张表是“人物”——他们的名字和简短的描述。主键位于“charname”上。
由于每部漫画（除了一些例外）都有多个角色，并且每个角色都出现在多部漫画中，因此在“角色”或“漫画”中放置一栏来反映这一点是不切实际的。相反，我创建了一个名为“comicchars”的第三表，它列出了哪些角色出现在哪些漫画中。由于该表本质上连接了两个表，因此它只需要两列：charname 和 Comicnum，并且主键位于这两列上。
Using a primary key on multiple tables comes in handy when you're using an intermediate table in a relational database.
I'll use a database I once made for an example and specifically three tables within that table. I creäted a database for a webcomic some years ago. One table was called "comics"—a listing of all comics, their titles, image file name, etc. The primary key was "comicnum".
The second table was "characters"—their names and a brief description. The primary key was on "charname".
Since each comic—with some exceptions—had multiple characters and each character appeared within multiple comics, it was impractical to put a column in either "characters" or "comics" to reflect that. Instead, I creäted a third table was called "comicchars", and that was a listing of which characters appeared in which comics. Since this table essentially joined the two tables, it needed but two columns: charname and comicnum, and the primary key was on both.
我们创建复合主键来保证组成单个记录的列值的唯一性。这是一个有助于防止插入不应重复的数据的约束。
即：如果所有学生 ID 和出生证明号码都唯一分配给一个人。那么，将一个人的主键设置为学生 ID 和出生证明号码的组合是一个好主意，因为这样可以防止您意外插入具有不同学生 ID 和相同出生证明的两个人。
We create composite primary keys to guarantee the uniqueness column values which compose a single record. It is a constraint which helps prevent insertion of data which should not be duplicated.
i.e: If all student Ids and birth certificate numbers are uniquely assigned to a single person. Then it would be a good idea to make the primary key for a person a composition of student id and birth certificate number because it would prevent you from accidentally inserting two people who have different student ids and the same birth certificate.