RDBMS 中的键有哪些不同类型？
RDBMS 中的键有哪些不同类型？请在您的答案中附上示例。
What are the different types of keys in RDBMS? Please include examples with your answer.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
(I) 超级键 – 用于唯一标识记录的属性或属性组合称为超级键。一个表可以有多个超级键。
例如超级键
等等，任何能够唯一标识记录的组合都将是超级键。
(II) 候选密钥 – 可以定义为最小超级密钥或不可约超级密钥。换句话说，唯一标识记录的属性或属性组合，但其真子集都不能唯一标识记录。
例如，候选键
对于上表，我们只有两个候选键（即不可约超级键）用于唯一标识表中的记录。 ID Key 可以唯一地标识记录，类似地，姓名和地址的组合可以唯一地标识记录，但是姓名和地址都不能用于唯一地标识记录，因为我们可能有两名姓名相似的员工，或者两名来自同一地点的员工。同一个房子。
(III) 主键 – 数据库设计者用来唯一标识表中每一行的候选键称为主键。主键可以由表的一个或多个属性组成。
例如主键 - 数据库设计者可以使用候选键之一作为主键。在这种情况下，我们将“ID”和“姓名、地址”作为候选键，我们将“ID”键视为主键，因为另一个键是多个属性的组合。
(IV) 外键 – 外键是一个基表中指向另一个表的候选键（通常是主键）的属性或属性组合。外键的目的是确保数据的引用完整性，即只允许出现在数据库中的值。
例如，外键 – 假设我们有另一个表，即部门表，其属性为“Department_ID”、“Department_Name”、“Manager_ID”、“Location_ID”，其中 Department_ID 作为主键。现在，Employee 表（从属表或子表）的 Department_ID 属性可以定义为外键，因为它可以引用 Departments 表（被引用表或父表）的 Department_ID 属性，外键值必须与中的现有值匹配父表或为 NULL。
(V) 复合键 – 如果我们使用多个属性来创建主键，则该主键称为复合键（也称为复合键或串联键）。
例如，对于复合键，如果我们使用“姓名，地址”作为主键，那么它将是我们的复合键。
(VI) 备用键 – 备用键可以是除主键之外的任何候选键。
例如，备用键是“姓名、地址”，因为它是唯一不是主键的其他候选键。
(VII)次要键——甚至不是超级键但仍可用于识别记录（不唯一）的属性称为次要键。
例如，辅助键可以是姓名、地址、工资、部门 ID 等，因为它们可以识别记录，但它们可能不是唯一的。
(I) Super Key – An attribute or a combination of attribute that is used to identify the records uniquely is known as Super Key. A table can have many Super Keys.
E.g. of Super Key
So on as any combination which can identify the records uniquely will be a Super Key.
(II) Candidate Key – It can be defined as minimal Super Key or irreducible Super Key. In other words an attribute or a combination of attribute that identifies the record uniquely but none of its proper subsets can identify the records uniquely.
E.g. of Candidate Key
For above table we have only two Candidate Keys (i.e. Irreducible Super Key) used to identify the records from the table uniquely. ID Key can identify the record uniquely and similarly combination of Name and Address can identify the record uniquely, but neither Name nor Address can be used to identify the records uniquely as it might be possible that we have two employees with similar name or two employees from the same house.
(III) Primary Key – A Candidate Key that is used by the database designer for unique identification of each row in a table is known as Primary Key. A Primary Key can consist of one or more attributes of a table.
E.g. of Primary Key - Database designer can use one of the Candidate Key as a Primary Key. In this case we have “ID” and “Name, Address” as Candidate Key, we will consider “ID” Key as a Primary Key as the other key is the combination of more than one attribute.
(IV) Foreign Key – A foreign key is an attribute or combination of attribute in one base table that points to the candidate key (generally it is the primary key) of another table. The purpose of the foreign key is to ensure referential integrity of the data i.e. only values that are supposed to appear in the database are permitted.
E.g. of Foreign Key – Let consider we have another table i.e. Department Table with Attributes “Department_ID”, “Department_Name”, “Manager_ID”, ”Location_ID” with Department_ID as an Primary Key. Now the Department_ID attribute of Employee Table (dependent or child table) can be defined as the Foreign Key as it can reference to the Department_ID attribute of the Departments table (the referenced or parent table), a Foreign Key value must match an existing value in the parent table or be NULL.
(V) Composite Key – If we use multiple attributes to create a Primary Key then that Primary Key is called Composite Key (also called a Compound Key or Concatenated Key).
E.g. of Composite Key, if we have used “Name, Address” as a Primary Key then it will be our Composite Key.
(VI) Alternate Key – Alternate Key can be any of the Candidate Keys except for the Primary Key.
E.g. of Alternate Key is “Name, Address” as it is the only other Candidate Key which is not a Primary Key.
(VII) Secondary Key – The attributes that are not even the Super Key but can be still used for identification of records (not unique) are known as Secondary Key.
E.g. of Secondary Key can be Name, Address, Salary, Department_ID etc. as they can identify the records but they might not be unique.
来自此处和这里：（在我用谷歌搜索你的标题之后）
From here and here: (after i googled your title)
Ólafur 忘记了代理密钥：
Ólafur forgot the surrogate key:
还存在一个唯一的密钥。
PRIMARY KEY 和 UNIQUE KEY 之间的主要区别在于 PRIMARY KEY 永远不会采用 NULL 值，而 UNIQUE KEY 可能采用 NULL 值。
另外，一张表中只能有一个主键，而唯一键可以有多个。
There also exists a UNIQUE KEY.
The main difference between PRIMARY KEY and UNIQUE KEY is that the PRIMARY KEY never takes NULL value while a UNIQUE KEY may take NULL value.
Also, there can be only one PRIMARY KEY in a table while UNIQUE KEY may be more than one.
还有一个替代键：如果一个非主要属性依赖于另一个非主要属性，就会出现这种情况。那时您现在不需要选择哪个键作为主键来拆分您的表。在这种情况下，请使用代理键而不是主键。通常此键是系统定义的，并且始终具有数值，并且其值通常会针对新行自动递增。例如：ms acces = 自动编号 &我的 SQL = 标识列 &预言=序列。
There is also a SURROGATE KEY: it occurs if one non prime attribute depends on another non prime attribute. that time you don't now to choose which key as primary key to split up your table. In that case use a surrogate key instead of a primary key. Usually this key is system defined and always have numeric values and its value often automatically incremented for new rows. Eg : ms acces = auto number & my SQL = identity column & oracle = sequence.
部分键：
是一组可以唯一标识弱实体且与同一所有者实体相关的属性。它有时被称为鉴别器。
备用键：
除主键之外的所有候选键都称为备用键。
人工密钥：
如果没有明显的密钥（无论是独立的还是复合的）可用，那么最后的方法就是通过为每个记录或事件分配唯一的编号来简单地创建一个密钥。这就是所谓的开发人工密钥。
复合键：
如果没有单个数据元素唯一标识构造中的出现情况，则组合多个元素来为该构造创建唯一标识符称为创建复合键。
自然键：
当存储在构造中的数据元素之一用作主键时，它被称为自然键。
Partial Key:
It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.
Alternate Key:
All Candidate Keys excluding the Primary Key are known as Alternate Keys.
Artificial Key:
If no obvious key, either stand alone or compound is available, then the last resort is to simply create a key, by assigning a unique number to each record or occurrence. Then this is known as developing an artificial key.
Compound Key:
If no single data element uniquely identifies occurrences within a construct, then combining multiple elements to create a unique identifier for the construct is known as creating a compound key.
Natural Key:
When one of the data elements stored within a construct is utilized as the primary key, then it is called the natural key.
分享我在网上阅读时经常维护的笔记，希望对某人有所帮助。
候选键或可用键
候选键是那些作为表主键候选的键。简单来说，我们可以理解，这种类型的键完全满足主键的所有要求，不为空并且具有唯一记录，是主键的候选键。因此，密钥的类型称为候选密钥。每个表必须至少有一个候选键，但同时可以有多个。
主键
这种被选择作为表主键的候选键称为主键。主键用于识别表。每个表只有一个主键。在 SQL Server 中，当我们为任何表创建主键时，会自动为该列创建聚集索引。
外键
外键是用于定义两个表之间关系的键。当我们想要实现两个表之间的关系时，我们使用外键的概念。它也称为引用完整性。我们可以为每个表创建多个外键。外键通常是一个表中的主键，在另一个表中显示为字段，其中第一个表与第二个表有关系。换句话说，如果我们有一个表 A，其主键 X 链接到表 B，其中 X 是 B 中的字段，那么 X 将是 B 中的外键。
备用键或辅助键
如果任何表有多个候选键，然后从这些候选键中选择主键后，其余候选键称为该表的备用键。就像这里我们可以举一个非常简单的例子来理解备用键的概念。假设我们有一个名为 Employee 的表，其中有两列 EmpID 和 EmpMail，两者都具有非空属性和唯一值。因此这两列都被视为候选键。现在我们将 EmpID 作为该表的主键，然后 EmpMail 称为备用键。
复合键
当我们在多个列上创建键时，该键称为复合键。像这里我们可以举个例子来理解这个特性。我有一个表 Student，其中有两列 Sid 和 SrefNo，我们在这两列上设置主键。那么这个密钥就称为复合密钥。
自然键
自然键是业务概念所特有的一个或多个现有数据属性。对于 Customer 表，有两个候选键，在本例中为 CustomerNumber 和 SocialSecurityNumber。链接 http://www.agiledata.org/essays/keys.html
代理键
引入一个新列，称为代理键，这是一个没有业务意义的键。其中一个示例是图 1 中的地址表的 AddressID 列。地址没有“简单”的自然键，因为您需要使用地址表的所有列来为其自身形成一个键（您可能会能够仅使用街道和邮政编码的组合，具体取决于您的问题域），因此在这种情况下引入代理键是更好的选择。链接 http://www.agiledata.org/essays/keys.html
唯一密钥
唯一键是超键——也就是说，在数据库组织的关系模型中，关系变量的一组属性，它保存在分配给该变量的所有关系中，不存在两个不同的元组（行）此集合中的属性具有相同的值
聚合或复合键
当组合多个列以形成唯一键时，它们的组合值用于访问每一行并保持唯一性。这些密钥称为聚合密钥或复合密钥。值不会组合，而是使用其数据类型进行比较。
简单键
简单键仅由一个属性组成。
超级键
超级键在关系模型中定义为关系变量 (relvar) 的一组属性，它认为在分配给该变量的所有关系中，不存在两个不同的元组（行）具有相同的值。该集合中的属性。同样，超级密钥也可以定义为相关变量的一组属性，相关变量的所有属性在功能上都依赖于该属性。
部分密钥或鉴别器密钥
它是一组可以唯一标识弱实体且与同一所有者实体相关的属性。它有时被称为鉴别器。
Sharing my notes which I usually maintain while reading from Internet, I hope it may be helpful to someone
Candidate Key or available keys
Candidate keys are those keys which is candidate for primary key of a table. In simple words we can understand that such type of keys which full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key. So thus type of key is known as candidate key. Every table must have at least one candidate key but at the same time can have several.
Primary Key
Such type of candidate key which is chosen as a primary key for table is known as primary key. Primary keys are used to identify tables. There is only one primary key per table. In SQL Server when we create primary key to any table then a clustered index is automatically created to that column.
Foreign Key
Foreign key are those keys which is used to define relationship between two tables. When we want to implement relationship between two tables then we use concept of foreign key. It is also known as referential integrity. We can create more than one foreign key per table. Foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second. In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B.
Alternate Key or Secondary
If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table. Like here we can take a very simple example to understand the concept of alternate key. Suppose we have a table named Employee which has two columns EmpID and EmpMail, both have not null attributes and unique value. So both columns are treated as candidate key. Now we make EmpID as a primary key to that table then EmpMail is known as alternate key.
Composite Key
When we create keys on more than one column then that key is known as composite key. Like here we can take an example to understand this feature. I have a table Student which has two columns Sid and SrefNo and we make primary key on these two column. Then this key is known as composite key.
Natural keys
A natural key is one or more existing data attributes that are unique to the business concept. For the Customer table there was two candidate keys, in this case CustomerNumber and SocialSecurityNumber. Link http://www.agiledata.org/essays/keys.html
Surrogate key
Introduce a new column, called a surrogate key, which is a key that has no business meaning. An example of which is the AddressID column of the Address table in Figure 1. Addresses don't have an "easy" natural key because you would need to use all of the columns of the Address table to form a key for itself (you might be able to get away with just the combination of Street and ZipCode depending on your problem domain), therefore introducing a surrogate key is a much better option in this case. Link http://www.agiledata.org/essays/keys.html
Unique key
A unique key is a superkey--that is, in the relational model of database organization, a set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples (rows) that have the same values for the attributes in this set
Aggregate or Compound keys
When more than one column is combined to form a unique key, their combined value is used to access each row and maintain uniqueness. These keys are referred to as aggregate or compound keys. Values are not combined, they are compared using their data types.
Simple key
Simple key made from only one attribute.
Super key
A superkey is defined in the relational model as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. Equivalently a super key can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent.
Partial Key or Discriminator key
It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity. It is sometime called as Discriminator.