SQL 关于主键和自增长标识列的修改

这篇文章我总结下在使用SQL查询或修改数据库的时候经常会碰到的两个问题，以及对这两个问题解决方法。

• 第一个问题：对主键的修改，修改主键经常会碰到主键约束而造成update语句无法像对于普通无约束字段的操作那样方便。
• 第二个问题：自增长标识列的修改，identity声明的标识字段在遭遇部分行的删除操作后，会默认自动继续增长而不会补全后增长。

开头先声明下我使用的开发环境是SQL Server 2008。

一、对主键的修改

主键值都会带有主键约束，当执行update操作或是其他操作的时候就会受到限制无法修改，解决的方法是：取消主键约束->删掉主键列->插入修改后的主键值。

（1）取消主键约束

取消主键约束的SQL语句如下：

alter table [表名] drop constraint [主键名]

输入表名和主键名时不要带中括号（下同），这里值得说明的是[主键名]并不是主键所在的字段名。主键名如果没有指定系统会自动分配一个，这时就需要查询主键名，查询主键名的方法如下：

--使用sql存储方式进行主键的查询
exec sp_pkeys @table_name='[表名]'
--查询表的详细信息，在其中找到主键名
sp_help [表名]

在这里顺便介绍下如何查询主键的列名，虽然这可能用的不多，但以下这个方法还是比较直观的：

select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_NAME='[表名]'

（2）删掉主键列

上一步总结了一些补充的知识，这一步就简单许多了，方法如下：

alter table [表名] drop column [字段名]

（3）插入修改后的主键名

alter table [表名] add constraint [主键名] primary key(字段名)

值得说明的是，这里的主键名是自定义的主键名，指定之后如果以后还需取消主键约束就可以用这个主键名，而不再是系统自动分配的字段名了。

二、自增长标识符的修改

这个问题主要遇到的情形是，在删除了一段连续的带有自增长字段的一部分数据项之后，自增长序列不会自动补全，而是按照原先的计数继续增长。举个例一段连续的自增长字段（1，2，3，…，8，9），我们将其中的（7，8，9）delete掉后再向其中插入新的数据，此时新序列标号不是7而是10。

解决的方法有两种：设置序列自增长开关和重置序列自增长起始数。

（1）设置序列自增长开关

取消序列的自增长的SQL语句：

set identity_insert [表名] on

这条语句执行完后，再进行插入时需要指定插入项的序列值，而不会引起序列的自增长。当修改完成后，如果还想让序列具有自增长性需要进行恢复操作：

set identity_insert [表名] off

（2）重置序列自增长起始数

dbcc checkident([表名],RESEED,[数字])

该语句可用来重置自增长序列的起始值，[数字]处设置起始值。

dbcc checkident([表名],NORESEED)

该语句可用来查询目前自增序列的当前值。