主键、唯一键和候选键的区别
主键、唯一键和候选键有什么区别？
What is difference between Primary Key, Unique Key and candidate key?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
主键、唯一键和候选键有什么区别？
What is difference between Primary Key, Unique Key and candidate key?
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
发布评论
评论（9）
超级键是唯一标识一条记录的一个或多个列（即属性）的集合。
候选密钥是一个最小的超级密钥。（这意味着我们不能从中删除任何属性，否则它将不再是超级密钥）。
主键是任意选择的候选键。必须只有且只有一个主键。我们可以选择任何候选键作为主键。
其他未被选为主键的候选键称为备用键。
如果主键有多于一列（或属性），则称为复合键。
Super Key is the set of one or more column (ie attributes) which uniquely identifies a record.
Candidate key is a minimal Super key.(it mean we cant remove any attributes from it otherwise it will not remain Superkey anymore).
Primary Key is a arbitrary selected Candidate key. There must be only and only One primary key. We can choose any candidate key as a Primary key.
Other candidate keys which are not chosen as Primary are called Alternate Keys.
If Primary Key have more then one column (or attributes) ,it is called Composite Key.
候选键是一个最小（即不可约）键，唯一键是一个冗长的概念，而主键是一个过时的概念，从人们认为一个键可能比其他键“更唯一”的时代起仍然存在。
Candidate key is a minimal (i.e. irreducible) key, unique key is a pleonasm, and primary key is an obsoleted concept still surviving from the days when people thought it possible for one key to be "more unique" than any of the others.
候选键 - 它是一个可以唯一标识数据库表的特定行或实例的所有列的列。您的护照 ID 是候选密钥的一个很好的示例，使用此 ID 时，您可以检索某个人的姓名以及与该特定人相关的其他详细信息。
主键 - 特定数据库表上可能有一些候选键，这些候选键是唯一的，可用于识别表中的特定实例，例如车辆登记号、底盘号、发动机序列号等...但请记住，只能有是一个将用作主键的候选键。
候选键与主键之间的区别：-
）主键和候选键都可以唯一标识数据库表中的记录。
2) 主键和候选键都具有 UNIQUE 和 NOT NULL 约束。
3) 主键或候选键可以是表中的单列或多列的组合。
Candidate keys - It is a column that can uniquely identify all columns for a specific row or instance of your database table. Your passport ID is a good example of a Candidate key and when using this ID you can retrieve a persons name, surname and other details related to that specific person.
Primary key - There may be a few Candidate keys on a specific database table which is unique and can be used to identify a specific instance in a table e.g. Vehicle registration number, Chassis number, engine serial number etc... but remember there can only be one candidate key that will be used as a primary key.
Difference between Candidate Key vs Primary Key:-
) Both Primary and Candidate keys can uniquely identify records in a table on database.
2) Both Primary and Candidate keys are has constraints UNIQUE and NOT NULL.
3) Primary key or Candidate keys can be either single column or combination of multiple columns in a table.
谷歌不再工作了吗？
http://en.wikipedia.org/wiki/Candidate_key
http://en.wikipedia.org/wiki/Unique_key
Google does not work anymore?
http://en.wikipedia.org/wiki/Candidate_key
http://en.wikipedia.org/wiki/Unique_key
候选键 – 候选键可以是任何可以作为数据库中唯一键的列或列的组合。一张表中可以有多个候选键。每个候选键都可以作为主键。
主键 – 主键是唯一标识一条记录的列或列的组合。只有一个候选键可以成为主键。
在选择主键时需要非常小心，因为错误的选择可能会对数据库架构师和未来的规范化产生不利影响。对于符合主键资格的候选键，它应该是非空的并且在任何域中都是唯一的。我经常观察到主键很少更改。我想了解您关于不更改主键的反馈。
查看更多文章
Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.
One needs to be very careful in selecting the Primary Key as an incorrect selection can adversely impact the database architect and future normalization. For a Candidate Key to qualify as a Primary Key, it should be Non-NULL and unique in any domain. I have observed quite often that Primary Keys are seldom changed. I would like to have your feedback on not changing a Primary Key.
For more articles
所有答案都缺少物理表征，并且其中一些答案不完整。所以这是图形表示的区别
说明
超级键
超级键是一组一个或多个键，可用于唯一标识表中的一条记录。
示例：主键、唯一键、备用键是超级键的子集。
候选键
候选键是一组一个或多个字段/列，可以唯一标识表中的记录。一张表中可以有多个候选键。每个候选键都可以作为主键。
示例：在上图中，
ID、RollNo和EnrollNo是候选键，因为所有这三个字段都可以用作主键。主键
主键是表中一组或多个字段/列的集合，唯一标识数据库表中的一条记录。它不能接受空值、重复值。只有一个候选键可以成为主键。
备用键
备用键是可以用作主键的键。基本上它是当前未用作主键的候选键。
示例：在上图中，当我们将
ID定义为主键时，RollNo和EnrollNo成为备用键。复合/复合键
复合键是表中多个字段/列的组合。它可以是候选键、主键。
唯一键
唯一键是表中一个或多个字段/列的集合，唯一标识数据库表中的一条记录。它类似于主键，但它只能接受一个空值，并且不能有重复的值。如需更多帮助，请参阅文章 主键之间的差异和唯一键。
外键
外键是数据库表中的一个字段，在另一个表中是主键。它可以接受多个空值、重复值。如需更多帮助，请参阅文章 主键之间的差异和外键。
示例：我们可以在 Employee 表中有一个
DeptID列，该列指向部门表中的DeptID列，并以主键为该列。来源
All the answers are missing physical representation and some of them are incomplete. So here is the difference by graphical representation
Explanation
Super Key
Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.
Example : Primary key, Unique key, Alternate key are subset of Super Keys.
Candidate Key
A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.
Example: In above diagram
ID,RollNoandEnrollNoare Candidate Keys since all these three fields can be work as Primary Key.Primary Key
Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.
Alternate key
Alternate key is a key that can be used as a primary key. Basically it is a candidate key that is currently not being used as the primary key.
Example: In above diagram
RollNoandEnrollNobecomes Alternate Keys when we defineIDas Primary Key.Composite/Compound Key
Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.
Unique Key
Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. For more help refer the article Difference between primary key and unique key.
Foreign Key
Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. For more help refer the article Difference between primary key and foreign key.
Example : We can have a
DeptIDcolumn in the Employee table which is pointing toDeptIDcolumn in a department table where it a primary key.source
复合键是具有超过 1 列的主键。例如，考虑学生和课程之间的多对多关系。这里我们需要另一个表，比如说学生课程。
该studentcourses 表将包含student_id 和course_id 作为其列。这将形成您的复合密钥。
候选键是一组可以唯一标识表中的值并可以充当唯一键的列。这些候选键之一将成为主键，其余的将成为备用键。
唯一键顾名思义，用于唯一标识表中的值。例如Emp_id等。每个候选键将充当唯一键。唯一键永远不能是
NULL值。A composite key is a primary key which has more than 1 column. For example consider the many to many relation between student and courses. Here we need another table , lets say studentcourses.
This studentcourses table will have student_id and course_id as its columns. This will form your composite key.
Candidate key is a set of columns which can uniquely identify the values in a table and can act as a unique key. One of these candidate keys will become the primary key and the rest will become alternate keys.
Unique key as the name suggests , is used to uniquely identify a value in a table. For example Emp_id etc. Every candidate key will act as a unique key. Unique key can never be a
NULLvalue.在继续讨论差异之前，了解每个术语的定义非常重要。
候选键：最小列集，可以唯一标识表中的每一行。最小意味着在不损害唯一标识表中行的能力的情况下，不可能进一步减少列。每个表必须至少有一个候选键，但同时可以有多个。
特定的候选键有时称为主键、辅助键或备用键。
主键：一组列（隐含地是基于这组列的索引），可以唯一地标识表中的每一行。因此，它必须是不包含任何 NULL 值的唯一索引。一张表中只能有一个主键。主键是从候选键中选择的。因此，主键是候选键的子集，或者可以被视为候选键的特殊情况。
为了获得最佳性能，请根据对性能最关键的查询仔细选择主键列。选择很少或从不更新的主列，因为修改主键索引的列是一项昂贵的操作。选择主键值时，请考虑使用任意值，而不是依赖从其他来源派生的值。
唯一键：唯一键是组成唯一索引的一列或一组列。唯一索引是具有唯一约束（防止重复值）的列或列集上的索引。
唯一键也可以存储NULL值。
[注意：MySQL 允许在具有唯一约束的列中使用多个 NULL 值，但这可能不适用于所有数据库。]
有关 NULL 行为的进一步阅读：
候选键和主键的主要区别
主键是候选键的一种特殊子集，一张表中只能有一个主键，而一张表中可以有多个候选键。
主键和唯一键的关键区别
主键不存储空值，而唯一键
确实如此。
一张表只能有一个主键，但可以有多个
唯一的键。
主键不允许您删除或修改数据。在
另一方面，唯一的密钥可以。
主键的目的是强制实体完整性，而
唯一键的目的是强制执行唯一数据。
参考资料：MySQL参考手册
Before proceeding to the difference, it is important to understand the definition of each term.
CANDIDATE KEY: A minimal set of columns that can uniquely identify every row in a table. Minimal means that no further reduction of columns is possible without jeopardizing the ability to uniquely identify a row in a table. Every table must have at least one candidate key but at the same time can have several.
Specific candidate keys are sometimes called primary keys, secondary keys, or alternate keys.
PRIMARY KEY: A set of columns—and by implication, the index based on this set of columns—that can uniquely identify every row in a table. As such, it must be a unique index that does not contain any NULL values. There can only be one primary key in a table. A primary key is chosen from candidate key. Primary key is thus a subset of candidate key or can be viewed as a specialized case of candidate key.
For best performance, choose the primary key columns carefully based on the most performance-critical queries. Choose primary columns that are rarely or never updated because modifying the columns of the primary key index is an expensive operation. When choosing primary key values, consider using arbitrary values rather than relying on values derived from some other source.
UNIQUE KEY: A unique key is a column or set of columns that comprises unique index. Unique index is an index on a column or set of columns that have a unique constraint (prevents duplicate values).
Unique key can also store NULL values.
[Note: MySQL allows multiple NULL values in a column with unique constraint, but this may not be true for all databases.]
Further reading on NULL behaviour:
Key difference between candidate key and primary key
Primary key is a specialized subset of candidate key and there can only be one primary key in a table whereas there can be multiple candidate keys in a table.
Key differences between primary key and unique key
The primary key does not store null values, whereas the unique key
does.
A table can only have one primary key, whereas it can have multiple
unique keys.
The primary key does not allow you to delete or modify the data. On
the other hand, a unique key does.
The primary key’s purpose is to enforce entity integrity, whereas the
unique key’s purpose is to enforce unique data.
References: MySQL Reference Manual
候选键：唯一标识关系/表中的元组/行的列/属性（或属性集）。
主键：也是唯一标识关系/表中的元组/行的列/属性（或属性集）。
唯一键：不是主键的候选键（因此，唯一键也可以唯一标识表中的一行）。
区别：
Candidate Key: a column/attribute (or set of attributes) that uniquely identifies tuples/rows in a relation/table.
Primary Key: is also a column/attribute (or set of attributes) that uniquely identifies tuples/rows in a relation/table.
Unique Keys: Candidate keys that are not Primary key (so, unique keys can also uniquely identify a row in a table).
Differences: