database 一个表中可以有多个主键吗？

Can I have multiple primary keys in a single table?

提问by Adam Pierce

Can I have multiple primary keys in a single table?

一个表中可以有多个主键吗？

回答by Adam Pierce

A Table can have a Composite Primary Keywhich is a primary key made from two or more columns. For example:

一个表可以有一个复合主键，它是由两列或更多列组成的主键。例如：

CREATE TABLE userdata (
  userid INT,
  userdataid INT,
  info char(200),
  primary key (userid, userdataid)
);

Update:Here is a linkwith a more detailed description of composite primary keys.

更新：这是一个包含复合主键更详细描述的链接。

回答by RB.

You can only have one primary key, but you can have multiple columns in your primary key.

您只能有一个主键，但主键中可以有多个列。

You can also have Unique Indexes on your table, which will work a bit like a primary key in that they will enforce unique values, and will speed up querying of those values.

您还可以在您的表上使用唯一索引，它的工作方式有点像主键，因为它们将强制执行唯一值，并加快对这些值的查询。

回答by Walter Mitty

A table can have multiple candidate keys. Each candidate key is a column or set of columns that are UNIQUE, taken together, and also NOT NULL. Thus, specifying values for all the columns of any candidate key is enough to determine that there is one row that meets the criteria, or no rows at all.

一个表可以有多个候选键。每个候选键是一个列或一组列，它们是 UNIQUE，放在一起，也不是 NULL。因此，为任何候选键的所有列指定值就足以确定有一行符合条件，或者根本没有行。

Candidate keys are a fundamental concept in the relational data model.

候选键是关系数据模型中的一个基本概念。

It's common practice, if multiple keys are present in one table, to designate one of the candidate keys as the primary key. It's also common practice to cause any foreign keys to the table to reference the primary key, rather than any other candidate key.

如果一个表中存在多个键，通常的做法是将候选键之一指定为主键。使表的任何外键引用主键而不是任何其他候选键也是常见的做法。

I recommend these practices, but there is nothing in the relational model that requires selecting a primary key among the candidate keys.

我推荐这些做法，但关系模型中没有任何东西需要在候选键中选择一个主键。

回答by Eye

This is the answer for both the main question and for @Kalmi's question of

这是主要问题和@Kalmi 问题的答案

What would be the point of having multiple auto-generating columns?

拥有多个自动生成列有什么意义？

This code below has a composite primary key. One of its columns is auto-incremented. This will work only in MyISAM. InnoDB will generate an error "ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key".

下面的这段代码有一个复合主键。它的一列是自动递增的。这仅适用于 MyISAM。InnoDB 会产生错误“ ERROR 1075 (42000): Incorrect table definition; can only a auto column and it must be defined as a key”。

DROP TABLE IF EXISTS `test`.`animals`;
CREATE TABLE  `test`.`animals` (
  `grp` char(30) NOT NULL,
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` char(30) NOT NULL,
  PRIMARY KEY (`grp`,`id`)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

回答by Manohar Reddy Poreddy

(Have been studying these, a lot)

（一直在研究这些，很多）

Candidate keys- A minimal column combination required to uniquely identify a table row.
Compound keys- 2 or more columns.

候选键- 唯一标识表行所需的最小列组合。
复合键- 2 列或更多列。

• Multiple Candidate keyscan exist in a table.
  • Primary KEY- Only one of the candidate keys that is chosenby us
  • Alternate keys- All othercandidate keys
    • Both Primary Key & Alternate keys can be Compound keys

• 一个表中可以存在多个候选键。
  • Primary KEY- 只有我们选择的候选键之一
  • 备用键- 所有其他候选键
    • 主键和备用键都可以是复合键

Sources:
https://en.wikipedia.org/wiki/Superkey
https://en.wikipedia.org/wiki/Candidate_key
https://en.wikipedia.org/wiki/Primary_key
https://en.wikipedia.org/wiki/Compound_key

来源：
https: //en.wikipedia.org/wiki/Superkey
https://en.wikipedia.org/wiki/Candidate_key
https://en.wikipedia.org/wiki/Primary_key
https://en.wikipedia.org /wiki/Compound_key

回答by Pieter Geerkens

Primary Key is very unfortunate notation, because of the connotation of "Primary" and the subconscious association in consequence with the Logical Model. I thus avoid using it. Instead I refer to the Surrogate Key of the Physical Model and the Natural Key(s) of the Logical Model.

主键是非常不幸的符号，因为“主”的含义以及与逻辑模型的潜意识关联。因此我避免使用它。相反，我指的是物理模型的代理键和逻辑模型的自然键。

It is important that the Logical Model for every Entity have at least one set of "business attributes" which comprise a Key for the entity. Boyce, Codd, Date et al refer to these in the Relational Model as Candidate Keys. When we then build tables for these Entities their Candidate Keys become Natural Keys in those tables. It is only through those Natural Keys that users are able to uniquely identify rows in the tables; as surrogate keys should always be hidden from users. This is because Surrogate Keys have no business meaning.

重要的是，每个实体的逻辑模型都至少有一组“业务属性”，这些“业务属性”构成了实体的键。Boyce、Codd、Date 等人在关系模型中将这些称为候选键。当我们为这些实体构建表时，它们的候选键成为这些表中的自然键。只有通过这些自然键，用户才能唯一标识表中的行；因为代理键应该始终对用户隐藏。这是因为代理键没有商业意义。

However the Physical Model for our tables will in many instances be inefficient without a Surrogate Key. Recall that non-covered columns for a non-clustered index can only be found (in general) through a Key Lookup into the clustered index (ignore tables implemented as heaps for a moment). When our available Natural Key(s) are wide this (1) widens the width of our non-clustered leaf nodes, increasing storage requirements and read accesses for seeks and scans of that non-clustered index; and (2) reduces fan-out from our clustered index increasing index height and index size, again increasing reads and storage requirements for our clustered indexes; and (3) increases cache requirements for our clustered indexes. chasing other indexes and data out of cache.

然而，在许多情况下，如果没有代理键，我们表的物理模型将是低效的。回想一下，非聚集索引的非覆盖列只能（通常）通过对聚集索引的键查找找到（暂时忽略作为堆实现的表）。当我们可用的自然键很宽时，这 (1) 会加宽我们的非聚集叶节点的宽度，增加存储要求和读取访问以查找和扫描该非聚集索引；(2) 减少聚集索引的扇出，增加索引高度和索引大小，再次增加聚集索引的读取和存储要求；(3) 增加对我们的聚集索引的缓存要求。从缓存中追逐其他索引和数据。

This is where a small Surrogate Key, designated to the RDBMS as "the Primary Key" proves beneficial. When set as the clustering key, so as to be used for key lookups into the clustered index from non-clustered indexes and foreign key lookups from related tables, all these disadvantages disappear. Our clustered index fan-outs increase again to reduce clustered index height and size, reduce cache load for our clustered indexes, decrease reads when accessing data through any mechanism (whether index scan, index seek, non-clustered key lookup or foreign key lookup) and decrease storage requirements for both clustered and nonclustered indexes of our tables.

这就是为 RDBMS 指定为“主键”的小代理键证明是有益的。当设置为聚簇键时，用于从非聚簇索引到聚簇索引的键查找和从相关表的外键查找，所有这些缺点都消失了。我们的聚集索引扇出再次增加以减少聚集索引的高度和大小，减少我们聚集索引的缓存负载，减少通过任何机制访问数据时的读取（无论是索引扫描、索引查找、非聚集键查找还是外键查找）并减少我们表的聚集索引和非聚集索引的存储要求。

Note that these benefits only occur when the surrogate key is both small and the clustering key. If a GUID is used as the clustering key the situation will often be worse than if the smallest available Natural Key had been used. If the table is organized as a heap then the 8-byte (heap) RowID will be used for key lookups, which is better than a 16-byte GUID but less performant than a 4-byte integer.

请注意，这些好处仅在代理键和集群键都较小时才会出现。如果使用 GUID 作为聚类键，情况通常会比使用最小的可用自然键更糟。如果表被组织为堆，那么 8 字节（堆）RowID 将用于键查找，这比 16 字节 GUID 好，但性能不如 4 字节整数。

If a GUID must be used due to business constraints than the search for a better clustering key is worthwhile. If for example a small site identifier and 4-byte "site-sequence-number" is feasible then that design might give better performance than a GUID as Surrogate Key.

如果由于业务限制而必须使用 GUID，那么搜索更好的聚类键是值得的。例如，如果一个小的站点标识符和 4 字节的“站点序列号”是可行的，那么该设计可能比作为代理键的 GUID 提供更好的性能。

If the consequences of a heap (hash join perhaps) make that the preferred storage then the costs of a wider clustering key need to be balanced into the trade-off analysis.

如果堆（可能是散列连接）的结果使其成为首选存储，则需要在权衡分析中平衡更广泛的集群键的成本。

Consider this example::

考虑这个例子::

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

where the tuple "(P_Id,LastName)" requires a uniqueness constraint, and may be a lengthy Unicode LastName plus a 4-byte integer, it would be desirable to (1) declaratively enforce this constraint as "ADD CONSTRAINT pk_PersonID UNIQUE NONCLUSTERED (P_Id,LastName)" and (2) separately declare a small Surrogate Key to be the "Primary Key" of a clustered index. It is worth noting that Anita possibly only wishes to add the LastName to this constraint in order to make that a covered field, which is unnecessary in a clustered index because ALL fields are covered by it.

其中元组“ (P_Id,LastName)”需要唯一性约束，并且可能是一个冗长的 Unicode LastName 加上一个 4 字节的整数，最好 (1) 声明性地将此约束强制执行为“ ADD CONSTRAINT pk_PersonID UNIQUE NONCLUSTERED (P_Id ,LastName)”和（2）分别声明一个小的代理键作为聚集索引的“主键”。值得注意的是，Anita 可能只希望将 LastName 添加到此约束中，以使其成为覆盖字段，这在聚集索引中是不必要的，因为所有字段都被它覆盖。

The ability in SQL Server to designate a Primary Key as nonclustered is an unfortunate historical circumstance, due to a conflation of the meaning "preferred natural or candidate key" (from the Logical Model) with the meaning "lookup key in storage" from the Physical Model. My understanding is that originally SYBASE SQL Server always used a 4-byte RowID, whether into a heap or a clustered index, as the "lookup key in storage" from the Physical Model.

SQL Server 中将主键指定为非集群的能力是一个不幸的历史环境，因为“首选自然键或候选键”（来自逻辑模型）的含义与物理模型中的“存储中的查找键”含义混为一谈。模型。我的理解是，最初 SYBASE SQL Server 总是使用一个 4 字节的 RowID，无论是进入堆还是聚集索引，作为物理模型中的“存储中的查找键”。

回答by Yet Another Geek

As noted by the others it is possible to have multi-column primary keys. It should be noted however that if you have some functional dependenciesthat are not introduced by a key, you should consider normalizingyour relation.

正如其他人所指出的，可以有多列主键。但是应该注意的是，如果您有一些不是由键引入的功能依赖项，您应该考虑规范化您的关系。

Example:

例子：

Person(id, name, email, street, zip_code, area)

There can be a functional dependency between id -> name,email, street, zip_code and areaBut often a zip_codeis associated with a areaand thus there is an internal functional dependecy between zip_code -> area.

之间可能存在函数依赖，id -> name,email, street, zip_code and area但通常 azip_code与 a 相关联，area因此在 之间存在内部函数依赖zip_code -> area。

Thus one may consider splitting it into another table:

因此，可以考虑将其拆分到另一个表中：

Person(id, name, email, street, zip_code)
Area(zip_code, name)

So that it is consistent with the third normal form.

使其符合第三范式。

回答by Bill Karwin

Some people use the term "primary key" to mean exactly an integer column that gets its values generated by some automatic mechanism. For example AUTO_INCREMENTin MySQL or IDENTITYin Microsoft SQL Server. Are you using primary key in this sense?

有些人使用术语“主键”来准确地表示一个整数列，它通过某种自动机制生成其值。例如AUTO_INCREMENT在 MySQL 或IDENTITYMicrosoft SQL Server 中。你在这个意义上使用主键吗？

If so, the answer depends on the brand of database you're using. In MySQL, you can't do this, you get an error:

如果是这样，答案取决于您使用的数据库品牌。在 MySQL 中，你不能这样做，你会得到一个错误：

mysql> create table foo (
  id int primary key auto_increment, 
  id2 int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; 
there can be only one auto column and it must be defined as a key

In some other brands of database, you are able to define more than one auto-generating column in a table.

在一些其他品牌的数据库中，您可以在一个表中定义多个自动生成列。

回答by Rusiru Adithya Samarasinghe

Having two primary keys at the same time, is not possible. But (assuming that you have not messed the case up with composite key), may be what you might need is to make one attribute unique.

同时拥有两个主键是不可能的。但是（假设您没有用复合键弄乱情况），您可能需要的是使一个属性唯一。

CREATE t1(
c1 int NOT NULL,
c2 int NOT NULL UNIQUE,
...,
PRIMARY KEY (c1)
);

However note that in relational database a 'super key' is a subset of attributes which uniquely identify a tuple or row in a table. A 'key' is a 'super key' that has an additional property that removing any attribute from the key, makes that key no more a 'super key'(or simply a 'key' is a minimal super key). If there are more keys, all of them are candidate keys. We select one of the candidate keys as a primary key. That's why talking about multiple primary keys for a one relation or table is being a conflict.

但是请注意，在关系数据库中，“超级键”是唯一标识表中元组或行的属性子集。“键”是一个“超级键”，它具有一个附加属性，可以从键中删除任何属性，使该键不再是“超级键”（或者简单地“键”是最小的超级键）。如果有更多的键，它们都是候选键。我们选择候选键之一作为主键。这就是为什么谈论一个关系或表的多个主键是一种冲突。

回答by HLGEM

A primary key is the key that uniquely identifies a record and is used in all indexes. This is why you can't have more than one. It is also generally the key that is used in joining to child tables but this is not a requirement. The real purpose of a PK is to make sure that something allows you to uniquely identify a record so that data changes affect the correct record and so that indexes can be created.

主键是唯一标识一条记录的键，用于所有索引。这就是为什么你不能拥有多个。它通常也是用于连接子表的键，但这不是必需的。PK 的真正目的是确保某些东西允许您唯一地标识记录，以便数据更改影响正确的记录，从而可以创建索引。

However, you can put multiple fields in one primary key (a composite PK). This will make your joins slower (espcially if they are larger string type fields) and your indexes larger but it may remove the need to do joins in some of the child tables, so as far as performance and design, take it on a case by case basis. When you do this, each field itself is not unique, but the combination of them is. If one or more of the fields in a composite key should also be unique, then you need a unique index on it. It is likely though that if one field is unique, this is a better candidate for the PK.

但是，您可以将多个字段放在一个主键（复合 PK）中。这将使您的连接变慢（特别是如果它们是较大的字符串类型字段）并且您的索引更大，但它可能会消除在某些子表中进行连接的需要，因此就性能和设计而言，将其视为一个案例案例基础。当您这样做时，每个字段本身都不是唯一的，但它们的组合是唯一的。如果组合键中的一个或多个字段也应该是唯一的，那么您需要在其上建立唯一索引。但是，如果一个字段是唯一的，这很可能是 PK 的更好候选者。

Now at times, you have more than one candidate for the PK. In this case you choose one as the PK or use a surrogate key (I personally prefer surrogate keys for this instance). And (this is critical!) you add unique indexes to each of the candidate keys that were not chosen as the PK. If the data needs to be unique, it needs a unique index whether it is the PK or not. This is a data integrity issue. (Note this is also true anytime you use a surrogate key; people get into trouble with surrogate keys because they forget to create unique indexes on the candidate keys.)

现在有时，您有不止一个 PK 候选人。在这种情况下，您选择一个作为 PK 或使用代理键（我个人更喜欢这种情况下的代理键）。并且（这很关键！）您为每个未被选为 PK 的候选键添加唯一索引。如果数据需要唯一，无论是不是PK，都需要一个唯一索引。这是一个数据完整性问题。（请注意，在您使用代理键的任何时候也是如此；人们会因为忘记在候选键上创建唯一索引而遇到代理键的麻烦。）

There are occasionally times when you want more than one surrogate key (which are usually the PK if you have them). In this case what you want isn't more PK's, it is more fields with autogenerated keys. Most DBs don't allow this, but there are ways of getting around it. First consider if the second field could be calculated based on the first autogenerated key (Field1 * -1 for instance) or perhaps the need for a second autogenerated key really means you should create a related table. Related tables can be in a one-to-one relationship. You would enforce that by adding the PK from the parent table to the child table and then adding the new autogenerated field to the table and then whatever fields are appropriate for this table. Then choose one of the two keys as the PK and put a unique index on the other (the autogenerated field does not have to be a PK). And make sure to add the FK to the field that is in the parent table. In general if you have no additional fields for the child table, you need to examine why you think you need two autogenerated fields.

有时您需要多个代理键（如果您拥有它们，通常是 PK）。在这种情况下，您想要的不是更多 PK，而是更多带有自动生成键的字段。大多数数据库不允许这样做，但有办法绕过它。首先考虑是否可以根据第一个自动生成的键（例如 Field1 * -1）计算第二个字段，或者可能需要第二个自动生成的键确实意味着您应该创建一个相关表。相关表可以是一对一的关系。您可以通过将 PK 从父表添加到子表，然后将新的自动生成的字段添加到表中，然后添加适合该表的任何字段来强制执行此操作。然后选择两个键之一作为 PK，并在另一个上放置唯一索引（自动生成的字段不必是 PK）。并确保将 FK 添加到父表中的字段。通常，如果子表没有其他字段，则需要检查为什么您认为需要两个自动生成的字段。