为什么我们不能有多个主键？
我知道表中不能有超过 1 个主键，但技术原因是什么？
I Know there can't be more than 1 primary key in a table but what is the technical reason ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
我知道表中不能有超过 1 个主键，但技术原因是什么？
I Know there can't be more than 1 primary key in a table but what is the technical reason ?
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
发布评论
评论（12）
直接从 SO 提取：
主键上下文中的主要意味着它的重要性排名第一。因此，只能有一把钥匙。根据定义，
它通常也是索引附加实际数据的键，即数据与主键索引一起存储。其他索引仅包含正在索引的数据，可能还包含一些包含的列。
Pulled directly from SO:
Primary in the context of Primary Key means that it's ranked first in importance. Therefore, there can only be one key. It's by definition.
It's also usually the key for which the index has the actual data attached to it, that is, the data is stored with the primary key index. Other indices contain only the data that's being indexed, and perhaps some Included Columns.
事实上，EFCodd（关系数据库模型的发明者）[1] 最初使用“主键”一词来表示关系中任意数量的键，而不仅仅是一个。他明确表示，很可能拥有不止一把这样的钥匙。他的建议是数据库设计者可以选择一个键作为首选标识符（“主键”） - 但原则上这是可选的，这样的选择是“任意的”（这是他的原话） 。由于所有密钥都具有相同的属性，因此没有必要选择其中任何一个。
后来 [2] Codd 最初称为主键的东西被称为“候选键”，而被挑选出来作为首选键的一个键被称为“主”键。然而，这并不是真正的根本性转变，因为主键与候选键的含义完全相同。由于它们是等效的概念，因此当我们说“必须”只有一个主键时，这并不意味着任何重要的事情。如果您有多个候选键，如果您愿意，您可以相当合理地将其中多个候选键称为“主键”，因为它不会对数据库的含义和功能产生任何逻辑或实际差异。
有人（我和其他人）认为，将每个表一个键指定为“主要”的想法是完全多余的，有时甚至会妨碍对数据库设计和数据完整性问题的良好理解。然而，这个概念是如此根深蒂固，我们可能会坚持下去。
所以你的问题的正确答案是“惯例”和“方便”。根本没有什么好的技术原因。
[1] 大型共享数据库的数据关系模型 (1970)
[2] 例如，“关系数据库模型的进一步规范化” (1971)
In fact E.F.Codd (the inventor of the Relational Database Model) [1] originated the term "primary key" to mean any number of keys of a relation - not just one. He made it clear that it was quite possible to have more than one such key. His suggestion was that the database designer could choose one key as a preferred identifier ("the primary key") - but in principle this was optional and such a choice was "arbitrary" (that was his word). Because all keys enjoy the same properties as each other there is no fundamental need to choose any one over another.
Later on [2] what Codd originally called primary keys became known as candidate keys and the one key singled out as the preferred one became known as the "primary" key. This was not really a fundamental shift however because a primary key means exactly the same as a candidate key. Since they are equivalent concepts it doesn't really mean anything important when we say there "must" only be one primary key. If you have more than one candidate key you could quite reasonably call more than one of them "primary" if you prefer because it doesn't make any logical or practical difference to the meaning and function of the database.
It has been argued (by me among others) that the idea of designating one key per table as "primary" is utterly superfluous and sometimes a positive hinderance to a good understanding of database design and data intgrity issues. However, the concept is so entrenched we are probably stuck with it.
So the proper answer to your question is "convention" and "convenience". There is no good technical reason at all.
[1] A Relational Model of Data for Large Shared Data Banks (1970)
[2] E.g. in "Further Normalization of the Relational Data Base Model" (1971)
好吧，它被称为“主要”是有原因的。例如，它是用于唯一标识记录的一个密钥...并且“可以是只有一个”。
您当然可以通过将索引放置在一个或多个唯一的其他字段上来模仿第二个“主”键，但出于数据库服务器的目的，通常只有当您的键不唯一时才有必要足以在合并复制情况下跨数据库服务器。 （即：多主控）。
Well, it's called "primary" for a reason. As in, its the one key used to uniquely identify the record... and there "can be only one".
You could certainly mimick a second "primary" key by having an index placed on one or more other fields that are unique but for the purposes of your database server it's generally only necessary if your key isn't unique enough to cross database servers in a merge replication situation. (ie: multi master).
PRIMARY KEY通常相当于UNIQUE INDEX NOT NULL。因此，您可以在单个表上有效地拥有多个“主键”。PRIMARY KEYis usually equivalent toUNIQUE INDEX NOT NULL. So you can effectively have multiple "primary keys" on a single table.主键是唯一标识该记录的键。
我不确定您是否在问 a) 是否可以有一个跨越多个列的主键，或者 b) 是否可以有多个唯一标识记录的键。
第一种是可能的，称为复合主键。
第二种也是可能的，但只有一种称为主键。
The primary key is the key which uniquely identifies that record.
I'm not sure if you're asking if a) there can be a single primary key spanning multiple columns, or b) if you can have multiple keys which uniquely identify the record.
The first is possible, known as a composite primary key.
The second is possible also, but only one is called the primary key.
因为“主键”中的“主”表示它的，嗯，奇点（？）。
但如果您需要更多，您可以定义具有完全相同行为的
UNIQUE键。Because the "primary" in "primary key" denotes its, mmm, singularity(?).
But if you need more, you can define
UNIQUEkeys which have quite the same behaviour.技术原因是只能有一个主要。不然也不会这么叫了。
但是，主键可以包含多个列 - 请参阅7.5.2。多列索引
The technical reason is that there can be only one primary. Otherwise it wouldn't be called so.
However a primary key can include several columns - see 7.5.2. Multiple-Column Indexes
主键是表中特定行的一个（可能是多个）唯一标识符。未指定为主要的其他唯一标识符因此通常被称为辅助唯一索引。
The primary key is the one (of possibly many) unique identifiers of a particular row in a table. The other unique identifiers, which were not designated as the primary one, are hence often refereed to as secondary unique indexes.
主键允许我们唯一地标识表中的每条记录。表中可以有 2 个主键，但它们称为复合主键。 “当您在表上定义多于一列作为主键时，称为复合主键。”
Primary key allows us to uniquely identify each record in the table. You can have 2 primary keys in a table but they are called Composite Primary Keys. "When you define more than one column as your primary key on a table, it is called a composite primary key."
主键定义记录的唯一性。使用两种不同的唯一性度量可能会出现问题。例如，如果您有主键 A 和 B，并且插入 A 相同而 B 不同的记录，那么这些记录是相同还是不同？如果您认为它们不同，则将主键设为 A 和 B 的组合。如果您认为它们是相同的记录，则只需使用 A 或 B 作为主键。
A primary key defines record uniqueness. To have two different measures of uniqueness can be problematic. For example, if you have primary keys A and B and you insert records where A is the same and B is different, then are those records the same or different? If you consider them different, then make your primary a composite of A and B. If you consider them the same record, then just use A or B as the primary key.
表上只能有一个主键，因为主键在表上创建一个聚集索引，该索引根据该主键列以有序方式将数据物理存储在叶节点上。
如果我们尝试在该表上创建另一个主键，那么将会出现与数据相关的一个主要问题。因为不能以两个不同的 2 顺序存储表的相同数据。
Only one primary key possible on the table because primary key creates a clustered index on the table which stored data physically on the leaf node in ordered way based on that primary key column.
If we try to create one another primary key on that table then there will be one major problem related to the data.Because be can not store same data of the table in two different-2 order.