PRIMARY KEY主键和IDENTITY属性函数的区别

1 PRIMARY KEY 约束

表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK)，用于强制表的实体完整性。在创建或修改表时，您可以通过定义 PRIMARY KEY约束来创建主键。一个表只能有一个PRIMARY KEY 约束。如果已存在 PRIMARY KEY 约束，则可以修改或删除它。例如，可以让表的 PRIMARY KEY 约束引用其他列，更改列的顺序、索引名、聚集选项或 PRIMARY KEY 约束的填充因子。但是，不能更改使用 PRIMARY KEY 约束定义的列长度。

注意：
若要修改 PRIMARY KEY 约束，必须先删除现有的 PRIMARY KEY 约束，然后再用新定义重新创建该约束。

为表中的现有列添加 PRIMARY KEY 约束时，SQL Server 2005 Database Engine 将检查现有列的数据和元数据以确保主键符合以下规则：

• 列不允许有空值。
  创建表时指定的 PRIMARY KEY 约束列隐式转换为 NOT NULL。
  
• 不能有重复的值。
  如果为具有重复值或允许有空值的列添加 PRIMARY KEY 约束，则数据库引擎 将返回一个错误并且不添加约束。

不能添加违反以上规则的 PRIMARY KEY 约束。

数据库引擎 会自动创建唯一的索引来强制实施 PRIMARY KEY 约束的唯一性要求。如果表中不存在聚集索引或未显式指定非聚集索引，则将创建唯一的聚集索引以强制实施 PRIMARY KEY 约束。

如果存在以下情况，则不能删除 PRIMARY KEY 约束：

• 如果另一个表中的 FOREIGN KEY 约束引用了 PRIMARY KEY 约束，则必须先删除 FOREIGN KEY 约束。
  
• 表包含应用于自身的 PRIMARY XML 索引。
  
  

一个表只能有一个 PRIMARY KEY 约束，并且 PRIMARY KEY约束中的列不能接受空值。由于 PRIMARY KEY 约束可保证数据的唯一性，因此经常对标识列定义这种约束。

如果为表指定了 PRIMARY KEY 约束，则 SQL Server 2005 Database Engine 将通过为主键列创建唯一索引来强制数据的唯一性。当在查询中使用主键时，此索引还可用来对数据进行快速访问。因此，所选的主键必须遵守的规则。

如果对多列定义了 PRIMARY KEY 约束，则一列中的值可能会重复，但来自 PRIMARY KEY 约束定义中所有列的任何值组合必须唯一。

 

  (1) 在数据库中创建主键：

    在表设计器中，单击要定义为主键的数据库列的行选择器。若要选择多个列，请在单击其他列的行选择器时按住 Ctrl 键。

<//ddue.schemas.microsoft.com/authoring/2003/5:content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">  <///ddue.schemas.microsoft.com/authoring/2003/5:content>右键单击该列的行选择器，然后选择“设置主键”。此时，将自动创建名为“PK_”（后跟表名）的主键索引，您可以在“索引/键”对话框中看到该索引。

 （2）使用SQL语句创建主键约束
使用SQL语句创建主键约束可以在创建表（Create Table）或修改表（Alter Table）时进行 创建。
在修改表中创建主键约束语法：
ALTER TABLE table_name
ADD
CONSTRAINT constraint_name
PRIMARY KEY [CLUSTERED | NONCLUSTERED]
{(Column[,…n])}
参数说明：
 CONSTRAINT：创建约束的关键字。
 constraint_name：创建约束的名称。
 PRIMARY KEY：表示所创建约束的类型为主键约束。
 CLUSTERED | NONCLUSTERED：是表示为PRIMARY KEY或UNIQUE约束创建聚集或非聚集索引的关键字。PRIMARY KEY约束默认为CLUSTERED，UNIQUE约束默认为 NONCLUSTERED。

 （3） 建表时创建PRIMARY KEY约束

CREATE TABLE mytable   --创建表
(
USERID int CONSTRAINT pk_id PRIMARY KEY,   --创建主键约束
USERNAME char (20),   --数据列

)

说明：在上段代码中，CONSTRAINT pk_id PRIMARY KEY为创建一个主键约束，pk_id为用户自定义的主键约束名称，但名称必须是合法的标识符。
  （4）添加PRIMARY KEY约束列

SQL语句如下：
ALTER TABLE mytable    --修改表
ADD    
CONSTRAINT pk_level
PRIMARY KEY CLUSTERED (userID)
GO --此时创建了一个pk_level聚集索引
说明：将某个数据列设置为主键约束时，该数据列不能为允许空，否则将会无法创建主键约束。
可以在查询分析器中写入如下代码将数据列设置为不允许空。
ALTER TABLE mytable    --修改表
ALTER Column USERLEVEL char (2) NOT NUL --将USERLEVEL数据列设置为不允许空

 

   （5） 删除PRIMARY KEY主键约束

通过删除主键约束来自动删除由该主键约束创建出来的聚集索引
alter table t drop constraint pk_level

2 IDENTITY（属性）(Transact-SQL)

在表中创建一个标识列。此属性与 CREATE TABLE 及 ALTER TABLE Transact-SQL 语句一起使用。

 

 

以下示例将使用 IDENTITY 属性，为自动递增标识号创建一个新表

CREATE TABLE new_employees
(
id_num int IDENTITY(0,1), --从0开始每次都增加1
fname varchar (20),
minit char(1),
lname varchar(30)
)

 

3 IDENTITY（函数）(Transact-SQL)

只用于在带有 INTO table 子句的 SELECT 语句中将标识列插入到新表中。

尽管类似，但是 IDENTITY函数不是与 CREATE TABLE 和 ALTER TABLE 一起使用的 IDENTITY 属性。

因为该函数在表中创建一个列，所以必须用下列方式中的一种在选择列表中指定该列的名称：

--(1) SELECT IDENTITY(int, 1,1) AS ID_Num INTO NewTable FROM OldTable --(2) SELECT ID_Num =IDENTITY(int, 1, 1) --int类型从1开始每次增加1个 INTO NewTable FROM OldTable --方法1和方法2达到的效果是一样的

 

4 数据库表中字段为主键并自增

1、把主键定义为自动增长标识符类型(主要是SQL Server)

create table a1(id int identity(1,1) primary key not null, name varchar(15));

insert into a1(name) values('111'),('22');

select id from a1;

 

2、在MySQL 中，如果把表的主键设为auto_increment类型，数据库就会自动为主键赋值。例如：

create table customers(id int auto_increment primary key not null, name varchar(15));

insert into customers(name) values("name1"),("name2");

select id from customers;

以上sql语句先创建了customers表，然后插入两条记录，在插入时仅仅设定了name字段的值。最后查询表中id字段，查询结果为：

id

1

2

由此可见，一旦把id设为auto_increment类型，mysql数据库会自动按递增的方式为主键赋值。

 

3 从序列中获取自动增长的标识符(主要是Oracle)

在Oracle中，可以为每张表的主键创建一个单独的序列，然后从这个序列中获取自动增加的标识符，把它赋值给主键。例如一下语句创建了一个名为customer_id_seq的序列，这个序列的起始值为1，增量为2。

 

create sequence customer_id_seq increment by 2 start with 1

 

一旦定义了customer_id_seq序列，就可以访问序列的curval和nextval属性。

curval：返回序列的当前值

nextval：先增加序列的值，然后返回序列值

 

以下sql语句先创建了customers表，然后插入两条记录，在插入时设定了id和name字段的值，其中id字段的值来自于customer_id_seq序列。最后查询customers表中的id字段。

 

create table customers(id int primary key not null, name varchar(15));

insert into customers values(customer_id_seq.curval, "name1"),(customer_id_seq.nextval, "name2");

select id from customers;

 

如果在oracle中执行以上语句，查询结果为：

id

1

3