数据库加主键sql
There are a couple of rules to follow when choosing a primary key for a table: all records in a primary key must be unique, a primary key cannot contain NULL values, a composite primary key cannot exceed 16 columns and a key length of 900 bytes, primary key values shouldn’t be changed
为表选择主键时，有两个规则要遵循：主键中的所有记录必须唯⼀，主键不能包含NULL值，复合主键不能超过16列且键长度为900字节，不应更改主键值
There are two types of a primary key – a natural key and a surrogate key and there is a lot of debating whether to choose one or another as a primary key
有两种类型的主键-⾃然键和代理键，关于是否选择⼀个或另⼀个作为主键的争论很多。 ⾃然键 (A natural key)
A natural key, otherwise called an intelligent or a domain key, is a key with a business value and logically related to a table, meaning that the data of a natural key exists in nature. If a more than one c
olumn is defined as a primary key on a table it is called a composite primary key. For example, the Customer table has a composite primary key combined from the FirstName, the LastName, and the Email columns:
⾃然密钥（也称为智能密钥或域密钥）是具有业务价值且在逻辑上与表相关的密钥，这意味着⾃然密钥的数据⾃然存在。 如果将⼀个以上的列定义为表的主键，则称为复合主键。 例如， Customer表具有⼀个组合的主键，该组合的主键由FirstName ， LastName和Email列组合⽽成： CREATE TABLE Customer
(
FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL,
Email varchar(20) NOT NULL,
PhoneNumber int NULL,
PRIMARY KEY CLUSTERED
(
FirstName,
LastName,
Email
))
使⽤⾃然键作为主键的优点 (Pros for using a natural key as a primary key)
A natural candidate key for a primary key already exists in a table – there is no need for adding additional column
表中已经存在⽤于主键的⾃然候选键-⽆需添加其他列
A natural key can be used in a client’s code as a search criteria
可以在客户代码中使⽤⾃然键作为搜索条件
使⽤⾃然键作为主键的缺点 (Cons for using a natural key as a primary key)
If a primary key is a combination of the several varchar columns it becomes large. SQL Server will automatically add a clustered index on a primary key, if a table already doesn’t have one. In this case an index also becomes big (much bigger on varchar data type columns than on an integer data type column) and the number of index pages which are used to store the index keys is increased. This increases the number of reads required to read the index and degrades overall index performance
如果主键是⼏个varchar列的组合，则它会变⼤。 如果表中没有索引，SQL Server会在主键上⾃动添加聚簇索引。 在这种情况下，索引也变⼤（varchar数据类型列上的索引⽐整数数据类型列上的索引⼤得多），并且⽤于存储索引键的索引页数也增加了。 这增加了读取索引所需的读取次数，并降低了整体索引性能
If a primary key column(s) have varchar data type the JOIN statements are slower as compared to the integer data-type joins
如果主键列具有varchar数据类型，则JOIN语句⽐整数数据类型的联接慢
In the following example a primary key is combined from three columns to achieve uniqueness of a primary key
在下⾯的⽰例中，主键由三列组合⽽成，以实现主键的唯⼀性
If the business logic changes, you’ll need to change all references to an existing primary key. Some examples are the change in ISBN (International Standard Book Number) from the 10 digit universal identifier into the 13 digit identifier. In the case of using SSN (Social Security Number) as a primary key you need to consider a possibility that SSNs may be reused after a person’s death and also that in cases of fraud, or an identity thefts an individual will get a new SSN.In the following example if a Customer’s SSN changes that change will have to be reflected in the two tables that reference the Customer table
如果业务逻辑发⽣变化，则需要将所有引⽤更改为现有的主键。 例如，ISBN（国际标准书号）从10位通⽤标识符更改为13位标识符。 在使⽤SSN（社会安全号码）作为主键的情况下，您需要考虑⼀个可能性，即⼀个⼈死亡后SSN可能会被重⽤，并且在欺诈或⾝份盗⽤的情况下，个⼈将获得⼀个新的SSN。在下⾯的⽰例中，如果客户的SSN更改必须在引⽤“客户”表的两个表中反映出来，
The record cannot be entered into a table until the value of a primary key is known.
在知道主键的值之前，不能将记录输⼊到表中。
代理键 (A surrogate key)
A surrogate key is a unique number generated by SQL Server or a database itself, and has no business logic. In SQL Server it is most often used as an IDENTITY column or a Globally Unique Identifiers (GUID) column – a globally unique 128 bit long data type.
代理密钥是由SQL Server或数据库本⾝⽣成的唯⼀数字，并且没有业务逻辑。 在SQL Server中，它最常⽤作IDENTITY列或Globally Unique Identifiers（GUID）列–全局唯⼀的128位长的数据类型。
使⽤代理键作为主键的优点 (Pros for using a surrogate key as a primary key)
If the business logic changes a surrogate key will not change since it has no business value
如果业务逻辑发⽣更改，则代理键不会更改，因为它没有业务价值
Surrogate keys are typically integers, which only require 4 bytes to store, so the primary key index structure will be smaller
代理键通常是整数，只需要存储4个字节，因此主键索引结构会更⼩
A naming system for a surrogate key is easier to create
代理键的命名系统更易于创建
使⽤代理键作为主键的缺点 (Cons for using a surrogate key as a primary key)
When a surrogate primary key is used an extra indexes may be required on the columns that used to be a part of a natural primary key. These indexes may be necessary to preserve uniqueness of those columns, and they may make an update of a table slower
当使⽤代理主键时，以前作为⾃然主键⼀部分的列可能需要额外的索引。 这些索引对于保留这些列的唯⼀性可能是必需的，并且它们可能会使表的更新变慢
Having a surrogate key may require an additional joins when searching a record. For example, the user can enter a known natural primary key (e.g. username) and retrieve wanted information (e.g. real name) based on a foreign key relationship with a natural key without accessing the primary key table. In a case of a surrogate key as a primary key the
user would have to look up in the primary key table to retrieve information stored in a table with a foreign key
relationship
搜索记录时，具有代理键可能需要其他联接。 例如，⽤户可以在不访问主键表的情况下基于与⾃然键
的外键关系来输⼊已知的⾃然主键（例如，⽤户名）并检索想要的信息（例如，真实姓名）。 在代理键作为主键的情况下，⽤户将不得不在主键表中查以检索存储在具有外键关系的表中的信息
A surrogate key cannot be used in a search
代理键不能在搜索中使⽤
性能测试 (Performance test)
The CPU time needed to parse and compile the JOIN statement with varchar data type columns and a composite primary key:
使⽤varchar数据类型列和复合主键解析和编译JOIN语句所需的CPU时间：
SET STATISTICS TIME ON
GO
SELECT c.FirstName
, c.LastName
, i.Quantity
FROM
dbo.Invoice i
INNER JOIN
dbo.Customer c
ON c.FirstName=i.CustomerFName
AND c.LastName=i.CustomerLName
AND c.Email=i.Email
GO
SET STATISTICS TIME OFF
GO
SQL Server parse and compile time:
CPU time = 4 ms, elapsed time = 4 ms.
(5 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
The parse and compile time of the JOIN statement with an integer data type primary key:
具有整数数据类型主键的JOIN语句的解析和编译时间：
SET STATISTICS TIME ON
GO
SELECT c.FirstName
, c.LastName
, i.Quantity
FROM
dbo.Invoice i
INNER JOIN
dbo.Customer c
ON c.CustomerID = i.CustomerID
GO
SET STATISTICS TIME OFF
GO
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
(5 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
重构现有的主键 (Refactoring an existing primary key)
In a situation when one key is already implemented on a table, but another type of a primary key is more suitable you can implement database refactorings and introduce a surrogate key to replace an existing natural key with, or replace an
existing surrogate key with a natural key
在⼀个键已经在表上实现⽽另⼀种类型的主键更适合的情况下，您可以实现数据库重构，并引⼊替代键以将现有的⾃然键替换为⾃然键，或将现有的替代键替换为⾃然键键
Choosing a primary key by replacing a natural key with a surrogate key is a method mostly used to reduce coupling between a database schema and external applications in cases when an existing natural key may change. Also, a large natural key may reduce performance
通过⽤替代键替换⾃然键来选择主键是⼀种在现有⾃然键可能发⽣更改的情况下减少数据库模式与外部应⽤程序之间耦合的⽅法。 此外，较⼤的⾃然键可能会降低性能
The opposite database refactoring method of introducing a surrogate key to a table is the replacing an existing surrogate key with a natural key. Motivation for using this refactoring is mostly to maintain a key strategy or to remove unnecessary keys – sometimes a surrogate key column is introduced to a table when it actually wasn’t needed
向表中引⼊替代键的相反数据库重构⽅法是⽤⾃然键替换现有的替代键。 使⽤这种重构的动机主要是为了维护密钥策略或删除不必要的密钥-有时在实际上不需要表时将替代密钥列引⼊表中
Regardless of a chosen primary key type for your database tables you should choose a single strate
gy and be consistent in applying it throughout your database. By refactoring your database to consolidate key strategy you can achieve code consistency because having a variety of keys your code to access a database is also implemented in various ways which increases the maintenance of code having your developers to follow all different approaches. Also, if your company has a corporate standard for a preferred key strategy you may discover that your schema doesn’t comply with the rules, so you’ll need to refactor your primary keys and implement the consolidate key strategy refactoring
⽆论为数据库表选择哪种主键类型，都应选择⼀种策略，并在整个数据库中应⽤它时保持⼀致。 通过重构数据库以整合关键策略，您可以实现代码⼀致性，因为拥有各种密钥的代码也可以通过多种⽅式实现，从⽽可以使代码的维护更加复杂，从⽽使开发⼈员可以遵循所有不同的⽅法。 另外，如果您的公司具有⾸选密钥策略的企业标准，则可能会发现您的架构不符合规则，因此您需要重构主密钥并实施合并密钥策略重构
有⽤的资源： (Useful resources:)
数据库加主键sql
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议