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

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

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

我推荐这些实践，但是关系模型中没有要求在候选键中选择主键。

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

例子:

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

id ->名称、电子邮件、街道、zip_code和区域之间可能存在功能依赖关系 但zip_code通常与区域相关联，因此zip_code ->区域之间存在内部函数依赖关系。

因此，可以考虑将它分割到另一个表中:

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

所以它和第三种形式是一致的。

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

你也可以在你的表上有唯一索引，这将有点像一个主键，因为它们将强制唯一的值，并将加快这些值的查询。

是的，在SQL中是可能的， 但是我们不能在MsAccess中设置多个主键。 我不知道其他数据库的情况。

CREATE TABLE CHAPTER (
    BOOK_ISBN VARCHAR(50) NOT NULL,
    IDX INT NOT NULL,
    TITLE VARCHAR(100) NOT NULL,
    NUM_OF_PAGES INT,
    PRIMARY KEY (BOOK_ISBN, IDX)
);

同时有两个主键是不可能的。但是(假设你没有把复合键搞砸)，你可能需要的是让一个属性是唯一的。

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.

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

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.

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.

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.

注意，这些好处只在代理键和集群键都很小的情况下才会出现。如果使用GUID作为集群键，情况通常会比使用最小的可用自然键更糟糕。如果表被组织为一个堆，那么8字节(堆)RowID将用于键查找，这比16字节的GUID更好，但性能不如4字节的整数。

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

如果堆的结果(可能是哈希连接)使其成为首选存储，那么需要在权衡分析中平衡更广泛的集群键的成本。

考虑这个例子:

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.

在SQL Server中将主键指定为非聚集的能力是一个不幸的历史情况，这是由于“首选自然或候选键”(来自逻辑模型)的含义与“存储中的查找键”(来自物理模型)的含义的合并。我的理解是，最初SYBASE SQL Server总是使用一个4字节的RowID，无论是在堆中还是在聚集索引中，作为物理模型中的“存储中的查找键”。