database 主键、唯一键和候选键的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3617032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between Primary Key, Unique Key and candidate key
提问by Ram
What is difference between Primary Key, Unique Key and candidate key?
主键、唯一键和候选键有什么区别？
采纳答案by Erwin Smout
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.
候选键是最小的（即不可约的）键，唯一键是一种pleonasm，而主键是一个过时的概念，从人们认为一个键可能比任何其他键“更独特”的时代起仍然存在。
回答by saurabh kimothi
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.
如果主键有多于一列（或属性），则称为复合键。
回答by Arunashis Paul
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.
候选键- 它是可以唯一标识特定行或数据库表实例的所有列的列。您的护照 ID 是候选键的一个很好的例子，当使用此 ID 时，您可以检索与该特定人员相关的人名、姓氏和其他详细信息。
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.
2) 主键和候选键都有约束 UNIQUE 和 NOT NULL。
3) Primary key or Candidate keys can be either single column or combination of multiple columns in a table.
3) 主键或候选键可以是表中的单列或多列的组合。
回答by gngolakia
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.
主键– 主键是唯一标识记录的列或列组合。只有一个候选键可以作为主键。在选择主键时需要非常小心，因为不正确的选择会对数据库架构师和未来的规范化产生不利影响。对于有资格作为主键的候选键，它应该是非空的并且在任何域中都是唯一的。我经常观察到主键很少改变。我想收到您关于不更改主键的反馈。
回答by Alex Reitbort
回答by user1212
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.
复合键是具有多于 1 列的主键。例如，考虑学生和课程之间的多对多关系。这里我们需要另一个表，比如说学生课程。
This studentcourses table will have student_id and course_id as its columns. This will form your composite key.
这个 studentcourses 表将有 student_id 和 course_id 作为它的列。这将形成您的复合键。
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.
顾名思义，唯一键用于唯一标识表中的值。例如 Emp_id 等。每个候选键都将充当唯一键。唯一键永远不能是NULL值。
回答by Manoz
Since 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, RollNoand EnrollNoare Candidate Keys since all these three fields can be work as Primary Key.
示例：在上图中ID，RollNo和EnrollNo是候选键，因为所有这三个字段都可以用作主键。
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
备用键
A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.
备用键是可以用作主键的键。基本上它是一个当前不是主键的候选键。
Example: In above diagram RollNoand EnrollNobecomes Alternate Keys when we define IDas Primary Key.
示例：在上图中RollNo，EnrollNo当我们定义ID为主键时就成为备用键。
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 to DeptIDcolumn in a department table where it a primary key.
示例：我们可以DeptID在 Employee 表中有一列指向DeptID部门表中的列，该列是主键。
