SQL 外键与辅助键

Foreign keys vs secondary keys

提问by Hossam Oukli

I used to think that foreign key and secondary key are the same thing.

我曾经认为外键和辅助键是一回事。

After Googling the result are even more confusing, some consider them to be the same, others said that a secondary key is an index that doesn't have to be unique, and allows faster access to data than with the primary key.

谷歌搜索结果更令人困惑，有人认为它们是相同的，有人说辅助键是一个索引，不必是唯一的，并且比使用主键可以更快地访问数据。

Can someone explain the difference?
Or is it indeed a case of mixed terminology?
Does it maybe differ per database type?

有人可以解释一下区别吗？
或者它确实是一个混合术语的情况？
它可能因数据库类型而异吗？

回答by NoChance

The definition in wiki/Foreign_keystates that:

wiki/Foreign_key 中的定义指出：

In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In other words, a foreign key is a column or a combination of columns that is used to establish and enforce a link between two tables.

The table containing the foreign key is called the referencing or child table, and the table containing the candidate key is called the referenced or parent table.

在关系数据库的上下文中，外键是一个表中的一个字段（或字段的集合），它唯一地标识了另一个表的一行。换句话说，外键是用于在两个表之间建立和强制链接的一列或列的组合。

包含外键的表称为引用表或子表，包含候选键的表称为被引用表或父表。

Take the example of the case:

以案例为例：

A customer may place 0,1 or more orders.

客户可能会下 0.1 个或更多订单。

From the point of the business, each customer is identified by a unique id (Primary Key) and instead of repeating the customer information with each order, we place a reference, or a pointer to that unique customer id (Customer's Primary Key) in the order table. By looking at any order, we can tell who placed it using the unique customer id.

从业务的角度来看，每个客户都由一个唯一的 id（主键）标识，而不是在每个订单中重复客户信息，我们在订单表。通过查看任何订单，我们可以使用唯一的客户 ID 判断是谁下的订单。

The relationship established between the parent (Customer table) and the child table (Order table) is established when you set the value of the FK in the Order table after the Customer row has been inserted. Also, deleting a child row may affect the parent depending on your Referential Integrity stings (Cascading Rules) established when the FK was created. FKs help establish integrity in a relational database system.

在插入 Customer 行后在 Order 表中设置 FK 的值时，在父表（Customer 表）和子表（Order 表）之间建立的关系就建立了。此外，删除子行可能会影响父行，具体取决于创建 FK 时建立的参照完整性刺痛（级联规则）。FK 有助于在关系数据库系统中建立完整性。

As for the "Secondary Key", the term refers to a structure of 1 or more columns that together help retrieve 1 or more rows of the same table. The word 'key' is somewhat misleading to some. The Secondary Key does not have to be unique (unlike the PK). It is not the Primary Key of the table. It is used to locate rows in the same table it is defined within (unlike the FK). Its enforcement is only through an index (either unique or not) and it is implementation is optional. A table could have 0,1 or more Secondary Key(s). For example, in an Employee table, you may use an auto generated column as a primary key. Alternatively, you may decide to use the Employee Number or SSN to retrieve employee(s) information.

至于“Secondary Key”，该术语是指由 1 个或多个列组成的结构，这些列一起帮助检索同一表的 1 个或多个行。“钥匙”这个词对某些人有些误导。辅助密钥不必是唯一的（与 PK 不同）。它不是表的主键。它用于在定义它的同一个表中定位行（与 FK 不同）。它的实施仅通过索引（唯一或不唯一）进行，并且它的实现是可选的。一个表可以有 0,1 个或更多的辅助键。例如，在 Employee 表中，您可以使用自动生成的列作为主键。或者，您可以决定使用员工编号或 SSN 来检索员工信息。

Sometimes people mix the term "Secondary Key" with the term "Candidate Key" or "Alternate Key" (usually appears in Normalization context) but they are all different.

有时人们将术语“辅助键”与术语“候选键”或“备用键”（通常出现在规范化上下文中）混在一起，但它们都是不同的。

回答by Zain Rizvi

A foreign key is a key that references an index on some other table. For example, if you have a table of customers, one of the columns on that table may be a country column which would just contain an ID number, which would match the ID of that country in a separate Country table. That country column in the customer table would be a foreign key.

外键是引用某个其他表上的索引的键。例如，如果您有一个客户表，则该表中的一列可能是仅包含 ID 号的国家/地区列，该 ID 号将与单独的 Country 表中该国家/地区的 ID 相匹配。客户表中的国家/地区列将是外键。

A secondary key on the other hand is just a different column in the table that you have used to create an index (which is used to speed up queries). Foreign keys have nothing to do with improving query speeds.

另一方面，辅助键只是表中用于创建索引（用于加速查询）的不同列。外键与提高查询速度无关。

回答by Greenstone Walker

"Secondary key" is not a term I'm familiar with. It doesn't appear in the index of Database Design for Mere Mortalsand I don't remember it in Pro SQL Server 2012 Relational Database Design and Implementation(my two "goto" books for database design). It also doesn't appear in the index for SQL for Smarties. It sounds like its not an actual term at all.

“辅助键”不是我熟悉的术语。它没有出现在Database Design for Mere Mortals的索引中，我不记得它出现在Pro SQL Server 2012 Relational Database Design and Implementation（我的两本数据库设计“goto”书籍）中。它也不会出现在SQL for Smarties的索引中。听起来它根本不是一个实际术语。

I've always used the term "candidate key".

我一直使用术语“候选密钥”。

A candidate key is a way to uniquely identify an entity. You identify all the candidate keys during the design phase of a database system. During the implementation phase, you will decide on a primary key: either one of the candidate keys or an artificial key. The primary key will probably be implemented with a primary key constraint; the candidate keys will probably be implemented with unique constraints.

候选键是唯一标识实体的一种方式。您可以在数据库系统的设计阶段确定所有候选键。在实施阶段，您将决定主键：候选键之一或人工键。主键可能会通过主键约束来实现；候选键可能会使用唯一约束来实现。

A foreign key is an instance of one entity's candidate key in another entity, representing a relationship between the two entities. It will probably be implemented with a foreign key constraints.

外键是一个实体在另一个实体中的候选键的实例，表示两个实体之间的关系。它可能会使用外键约束来实现。