database 一个数据库表可以包含多个主键吗？

Can a database table contains more than one primary key?

提问by Yatendra Goel

Can a database table contains more than one primary key?

一个数据库表可以包含多个主键吗？

Yes, I am talking about RDBMS.

是的，我说的是 RDBMS。

采纳答案by Zaki

The primary key of a relational table uniquely identifies each record in the table. So, in order to keep the uniqueness of each record, you cant have more than one primary key for the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys may consist of a single attribute or multiple attributes in combination.

关系表的主键唯一标识表中的每条记录。所以，为了保持每条记录的唯一性，表的主键不能超过一个。它可以是保证唯一的普通属性（例如每个人不超过一个记录的表中的社会安全号码），也可以是由 DBMS 生成的（例如全局唯一标识符或 GUID，在 Microsoft SQL Server 中）。主键可以由单个属性或多个属性的组合组成。

回答by cletus

A table can have:

一个表可以有：

• No primary keys;
• One primary key consisting of one column; or
• One composite primary key consisting of two or more columns.

• 没有主键；
• 一个主键由一列组成；或者
• 一个复合主键由两列或更多列组成。

Other than that you can have any number of unique indexes, which will do basically the same thing.

除此之外，您可以拥有任意数量的唯一索引，它们的作用基本相同。

回答by Ali

Yes, you can have Composite primary keys, that is, having two fields as a primary key.

是的，您可以拥有复合主键，即有两个字段作为主键。

回答by Erwin Smout

"First of all, you have to understand the history of entity-relationship design methodology as well as understand the word "relational" in relational database management systems (RDBMS)."

“首先，您必须了解实体关系设计方法的历史，以及了解关系数据库管理系统 (RDBMS) 中的“关系”一词。”

May I suggest politely that you first get YOURSELF educated on these very same subjects before leading other people into flawed beliefs ? I'll respond to the two worst ones of your stupidities below.

我是否可以礼貌地建议您先在这些非常相同的主题上接受教育，然后再将其他人带入有缺陷的信念中？我会回应你下面两个最糟糕的愚蠢行为。

"According to relational methodology principles, each entity should only have one and only one means to identify it."

“根据关系方法论原则，每个实体应该只有一种且只有一种方法来识别它。”

That is about the biggest crap I have ever heard anybody spawn around about relational data design. The relational model does not constrain any "entity", as you erroneously call it, to have any precise number of keys. Any "entity" can have any number of keys, and EACH key is, by definition of its very property of making the "rows" unique, a valid candidate for any purpose of "identification". Choosing the most useful/appropriate one for use in certain contexts (foreign keys in referencing tables, e.g.), is a design issue, and the relational model does not have anything to say on such things.

这是我听过的关于关系数据设计的最大废话。关系模型不会限制任何“实体”，正如您错误地称呼的那样，具有任何精确数量的键。任何“实体”都可以有任意数量的键，并且根据其使“行”唯一的属性的定义，每个键都是用于“识别”的任何目的的有效候选者。选择最有用/最合适的用于某些上下文（例如，引用表中的外键）是一个设计问题，关系模型对此类事情没有任何说明。

"Therefore, "R"DBMS attempts to facilitate the modeling of entity relationships."

“因此，“R”DBMS 试图促进实体关系的建模。”

Codd's paper "A Relational model of date for large shared data banks", which marks the birth of the relational model, predates the invention of E-R by a number of years. So to say that the Relational model attempts to facilitate the modeling of E-R concepts, is having things COMPLETELY backwards, and nothing but a display of one's own complete and utter ignorance of "the history" that you referred to in your own answer.

Codd 的论文“A Relational model of date for large shared database”标志着关系模型的诞生，比ER的发明早了很多年。因此，可以说关系模型试图促进 ER 概念的建模，就是让事物完全倒退，而只是展示了自己对您在自己的答案中提到的“历史”的完全无知。

回答by nvogel

The short answer is yes. A primary key is a candidate key and is in principle no different to any other candidate key. It is a widely observed convention that one candidate key per table is designated as the "primary" one - meaning that it is "preferred" or has some special meaning for the database designer or user. This is just convention however. It is only a label of convenience and a reminder about the potential significance of one key. In practice all keys can serve the same purpose and the "primary" one is not special or unique in any fundamental way.

简短的回答是肯定的。主键是候选键，原则上与任何其他候选键没有区别。将每个表的一个候选键指定为“主要”键是一种广泛观察的约定，这意味着它是“首选”或对数据库设计者或用户具有某些特殊含义。然而，这只是惯例。它只是一个方便的标签，也是一个关于一把钥匙潜在意义的提醒。实际上，所有键都可以用于相同的目的，“主要”键在任何基本方式上都不是特殊或唯一的。

回答by junmats

That's why it is called Primary Key because it is, well, PRIMARY

这就是为什么它被称为主键，因为它是主键

回答by Blessed Geek

First of all, you have to understand the history of entity-relationship design methodology as well as understand the word "relational" in relational database management systems (RDBMS).

首先，您必须了解实体-关系设计方法的历史，以及了解关系数据库管理系统 (RDBMS) 中的“关系”一词。

In order to define the bounds of an entity and relationships to be formed, there must be a unique handle or a unique combination of handles to identify each single instance of an entity and then to form relationships between them.

为了定义实体的边界和要形成的关系，必须有一个唯一的句柄或句柄的唯一组合来标识实体的每个单个实例，然后在它们之间形成关系。

You also need to understand the meaning/root of the word "identify" which is to zero in on the "identity" of each instance of an entity. "identity" being the mathematical term meaning "one" or a singularity.

您还需要了解“identify”一词的含义/词根，即实体的每个实例的“身份”为零。“身份”是数学术语，意思是“一个”或一个奇点。

According to relational methodology principles, each entity should only have one and only one means to identify it. Therefore, "R"DBMS attempts to facilitate the modeling of entity relationships. Note the differences between "Entity/Class" and "Entity/Class instance".

根据关系方法论原则，每个实体应该只有一种并且只有一种方法来识别它。因此，“R”DBMS 试图促进实体关系的建模。请注意“实体/类”和“实体/类实例”之间的区别。

However, RDBMS is used widely and mostly by people not so interested in accurately portraying the E-R design principles. So that frequently, we have more than one possible entity-definition sitting inside a table, which I call entity-aliasing. Opposed to identity-aliasing, where two or more instances of an entity-set hides under the same key, entity-aliasing is like the table

然而，RDBMS 被广泛使用，并且主要由对准确描绘 ER 设计原则不太感兴趣的人使用。所以经常，我们有不止一个可能的实体定义坐在一张桌子里，我称之为实体别名。与身份别名（identity-aliasing）相反，实体集的两个或多个实例隐藏在同一个键下，实体别名就像表

EmpProj([empId], empName, empAddr, projId, projLoc)

actually has two entity-sets aliased under the same table:

实际上在同一个表下有两个实体集别名：

Emp([empId], empName, empAddr)
Proj([projId], projLoc, empId)

That is when normalisation comes in - to separate these entities out. Try as we might to do a decent design normalisation, computer scientists may not have as good a perspective on the information as a statistician. The computer scientist (which in this discussion includes everyone with a decent knowledge of ER design) tries his/her best in creating a schema that cleanly defines entities and their relationships.

那就是规范化的时候 - 将这些实体分开。尽我们所能进行体面的设计规范化，计算机科学家可能不像统计学家那样对信息有很好的看法。计算机科学家（在本讨论中包括对 ER 设计有一定了解的每个人）尽力创建一个清晰定义实体及其关系的模式。

However, after 18 months analysing voluminous information from the database, the statistician begin to see principal components that emerge whose analyses is terribly crippled due to the misalignment of the principal components with those of boundaries of the computer scientists' perceived entities.

然而，在对数据库中的大量信息进行 18 个月的分析后，统计学家开始看到出现的主成分，由于主成分与计算机科学家感知实体的边界不一致，其分析严重受损。

That is where alternate unique keys are good for - to identify instances of entities due to the principal components existing as ghost-entities in the database.

这就是备用唯一键的好处所在 - 由于主要组件在数据库中作为幽灵实体存在，因此可以识别实体的实例。

Therefore, the primary key of a table is because that table is perceived to be a perfect entity as an entity should have only one primary key, be it singular or composite.

因此，表的主键是因为该表被认为是一个完美的实体，因为一个实体应该只有一个主键，无论是单键还是复合键。

As far as the statistician is concerned, even though the database allows only one primary key per table, the alternative unique keys is to the statistician the primary keys to those ghost-entities. Which is why sometimes you are frustrated by statisticians who seem to do double work by downloading the data into the local database of their workstation/PC.

就统计员而言，即使数据库只允许每个表有一个主键，对于统计员来说，替代的唯一键是那些幽灵实体的主键。这就是为什么有时您会对那些似乎通过将数据下载到他们的工作站/PC 的本地数据库来做双重工作的统计人员感到沮丧的原因。

In conclusion, the constraint placed by the "R"DBMS manufacturer in allowing only one primary key per table is their pretense in believing that they know how information behave and believing that principal components of the information due to the population do not mutate over time.

总之，“R”DBMS 制造商对每个表只允许一个主键的限制是他们假装相信他们知道信息的行为方式，并相信信息的主要组成部分不会随着时间发生变化。

If you have more than one unique keys possible in a table it means either one or more of the possibilities

如果表中可能有多个唯一键，则意味着一种或多种可能性

1. Like myself, you are lazy to separate them since they seem to work quite well
2. For performance' sake, mixing the entities into the same table makes the application run incredibly faster
3. Like the statistician, you gradually discover ghost entities in your information.

1. 像我一样，你懒得把它们分开，因为它们似乎工作得很好
2. 出于性能考虑，将实体混合到同一个表中可以使应用程序运行得非常快
3. 像统计学家一样，你会逐渐在你的信息中发现幽灵实体。