数据库加主键sql_SQL数据库设计：选择主键

数据库加主键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 column 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 an