I googled a lot, but I did not find the exact straight forward answer with an example.
我google了很多，但我没有找到一个例子的确切直接答案。
Any example for this would be more helpful.
任何这方面的例子都会更有帮助。
6 个解决方案
#1
38
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 Person table, 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”的意思，但是当你在地址表的State列中看到'WI'时，你知道你正在谈论的状态而不需要查看你所在州的哪个州表。
#2
5
A surrogate key is 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 key is the identifying column or set of columns of a table. Can be surrogate key or 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
)
#3
3
All keys are identifiers used as surrogates for the things they identify. E.F.Codd explained the concept of system-assigned surrogates as follows [1]:
所有密钥都是用作其识别内容的代理的标识符。 E.F.Codd解释了系统指定代理的概念如下[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]扩展数据库关系模型以捕获更多意义，E.F.Codd，1979
#4
1
This is a great treatment describing the various kinds of keys:
这是描述各种键的绝佳方法：
http://www.agiledata.org/essays/keys.html
http://www.agiledata.org/essays/keys.html
#5
1
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值。大多数表应该有一个主键，每个表只能有一个主键。
http://www.databasejournal.com/features/mssql/article.php/3922066/SQL-Server-Natural-Key-Verses-Surrogate-Key.htm
#6
0
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.
当您使用标识列更改组合键时，它通常可以帮助您使用代理键。
#1
38
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 Person table, 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”的意思，但是当你在地址表的State列中看到'WI'时，你知道你正在谈论的状态而不需要查看你所在州的哪个州表。
#2
5
A surrogate key is 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 key is the identifying column or set of columns of a table. Can be surrogate key or 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
)
#3
3
All keys are identifiers used as surrogates for the things they identify. E.F.Codd explained the concept of system-assigned surrogates as follows [1]:
所有密钥都是用作其识别内容的代理的标识符。 E.F.Codd解释了系统指定代理的概念如下[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]扩展数据库关系模型以捕获更多意义，E.F.Codd，1979
#4
1
This is a great treatment describing the various kinds of keys:
这是描述各种键的绝佳方法：
http://www.agiledata.org/essays/keys.html
http://www.agiledata.org/essays/keys.html
#5
1
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值。大多数表应该有一个主键，每个表只能有一个主键。
http://www.databasejournal.com/features/mssql/article.php/3922066/SQL-Server-Natural-Key-Verses-Surrogate-Key.htm
#6
0
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.
当您使用标识列更改组合键时，它通常可以帮助您使用代理键。