SQL 主键和代理键有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36773011/
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
What is the difference between a primary key and a surrogate key?
提问by Dom
I googled a lot, but I did not find the exact straight forward answer with an example.
我用谷歌搜索了很多，但我没有找到一个例子的确切直接答案。
Any example for this would be more helpful.
任何这方面的例子都会更有帮助。
回答by Bacon Bits
The primary key is a unique key in your table that you choose that best uniquely identifies a record in the table. All tables should have a primary key, because if you ever need to update or delete a record you need to know how to uniquely identify it.
主键是您选择的表中的唯一键，可以最好地唯一标识表中的记录。所有表都应该有一个主键，因为如果您需要更新或删除记录，您需要知道如何唯一标识它。
A surrogate key is an artificially generated key. They're useful when your records essentially have no natural key (such as a Persontable, since it's possible for two people born on the same date to have the same name, or records in a log, since it's possible for two events to happen such they they carry the same timestamp). Most often you'll see these implemented as integers in an automatically incrementing field, or as GUIDs that are generated automatically for each record. ID numbers are almost always surrogate keys.
代理键是人工生成的键。当您的记录基本上没有自然键时，它们很有用（例如Person表格，因为两个出生在同一日期的人可能具有相同的名字，或者日志中的记录，因为两个事件可能发生，例如他们携带相同的时间戳）。大多数情况下，您会看到这些实现为自动递增字段中的整数，或为每个记录自动生成的 GUID。ID 号几乎总是代理键。
Unlike primary keys, not all tables need surrogate keys, however. If you have a table that lists the states in America, you don't really need an ID number for them. You could use the state abbreviation as a primary key code.
然而，与主键不同的是，并非所有表都需要代理键。如果您有一张列出美国各州的表格，则您实际上并不需要它们的 ID 号。您可以使用州缩写作为主键代码。
The main advantage of the surrogate key is that they're easy to guarantee as unique. The main disadvantage is that they don't have any meaning. There's no meaning that "28" is Wisconsin, for example, but when you see 'WI' in the State column of your Address table, you know what state you're talking about without needing to look up which state is which in your State table.
代理键的主要优点是它们很容易保证是唯一的。主要的缺点是它们没有任何意义。例如，“28”没有意思是威斯康星州，但是当您在地址表的州列中看到“WI”时，您就知道您在谈论哪个州，而无需查找您所在州的哪个州桌子。
回答by tobypls
A surrogate keyis a made up value with the sole purpose of uniquely identifying a row. Usually, this is represented by an auto incrementing ID.
甲代理键是与唯一地识别行的唯一目的一个组成值。通常，这由自动递增的 ID 表示。
Example code:
示例代码：
CREATE TABLE Example
(
SurrogateKey INT IDENTITY(1,1) -- A surrogate key that increments automatically
)
A primary keyis the identifying column or set of columns of a table. Can be surrogate keyor any other unique combination of columns (for example a compound key). MUST be unique for any row and cannot be NULL.
甲主键是所述识别列或一组的表的列。可以是代理键或任何其他唯一的列组合（例如复合键）。对于任何行必须是唯一的，不能是NULL。
Example code:
示例代码：
CREATE TABLE Example
(
PrimaryKey INT PRIMARY KEY -- A primary key is just an unique identifier
)
回答by nvogel
All keys are identifiers used as surrogates for the things they identify. E.F.Codd explained the concept of system-assignedsurrogates as follows [1]:
所有的键都是标识符，用作它们所识别事物的代理。EFCodd 解释了系统分配代理的概念如下 [1]：
Database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them.
数据库用户可能会导致系统生成或删除代理，但他们无法控制其值，也不会向他们显示其值。
This is what is commonly referred to as a surrogate key. The definition is immediately problematic however because Codd was assuming that such a feature would be provided by the DBMS. DBMSs in general have no such feature. The keys are normally visible to at least some DBMS users as, for obvious reasons, they have to be. The concept of a surrogate has therefore morphed slightly in usage. The term is generally used in the data management profession to mean a key that is not exposed and used as an identifier in the business domain. Note that this is essentially unrelated to how the key is generated or how "artificial" it is perceived to be. All keys consist of symbols invented by humans or machines. The only possible significance of the term surrogate therefore relates how the key is used, not how it is created or what its values are.
这就是通常所说的代理键。然而，该定义立即有问题，因为 Codd 假设 DBMS 会提供这样的功能。DBMS 一般没有这样的功能。这些键通常至少对某些 DBMS 用户可见，因为显而易见的原因，它们必须如此。因此，代理的概念在用法上略有变化。该术语通常用于数据管理专业，表示未公开并用作业务领域中的标识符的密钥. 请注意，这与密钥的生成方式或它被视为“人为”的方式本质上无关。所有的钥匙都由人类或机器发明的符号组成。因此，代理一词的唯一可能意义与密钥的使用方式有关，而不是密钥的创建方式或其值是什么。
[1] Extending the database relational model to capture more meaning, E.F.Codd, 1979
[1] 扩展数据库关系模型以获取更多含义，EFCodd，1979
回答by n8wrl
This is a great treatment describing the various kinds of keys:
这是描述各种键的很好的处理方式：
回答by Bishoy Frank
A surrogate key is typically a numeric value. Within SQL Server, Microsoft allows you to define a column with an identity property to help generate surrogate key values.
代理键通常是一个数值。在 SQL Server 中，Microsoft 允许您定义具有标识属性的列以帮助生成代理键值。
The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key.
PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含 UNIQUE 值。主键列不能包含 NULL 值。大多数表应该有一个主键，每个表只能有一个主键。
回答by Fernando Gutierrez
I think Michelle Poolet describes it in a very clear way:
我认为 Michelle Poolet 以非常清晰的方式描述了它：
A surrogate key is an artificially produced value, most often a system-managed, incrementing counter whose values can range from 1 to n, where n represents a table's maximum number of rows. In SQL Server, you create a surrogate key by assigning an identity property to a column that has a number data type.
代理键是人工生成的值，通常是系统管理的递增计数器，其值的范围可以从 1 到 n，其中 n 表示表的最大行数。在 SQL Server 中，您可以通过将标识属性分配给具有数字数据类型的列来创建代理键。
http://sqlmag.com/business-intelligence/surrogate-key-vs-natural-key
http://sqlmag.com/business-intelligence/surrogate-key-vs-natural-key
It usually helps you use a surrogate key when you change a composite key with an identity column.
当您使用标识列更改组合键时，它通常可以帮助您使用代理键。
