如何在SQL SERVER 2008中修改主键Id的值

今天遇到一个客户的奇葩需求，需要修改主键Id的值。由于之前测试时有些Id较小的数据被删除掉了，所以数据的编号不是连续的，如下（例子做了简化），客户需要将T3、T4的Id修改为3、4：

    Id    Name
    1     T1
    2     T2
    ...   //测试数据被删
    101   T3
    102   T4

由于Id字段设置了主键自增标识，无法直接UPDATE，所以首先想到的是怎么把Id字段的自增标识删除掉，更新完了以后再重新设置回来。在网上搜索获知，可以通过更新系统表syscolumns的colstat值来达到这个目的。但是要想修改syscolumns这个系统表，必须将数据库设置成允许修改系统表。此操作可以在SQL Server Enterprise Manager里面选择数据库服务器，按右键，选择“属性”，在“服务器设置”页面中将“允许对系统目录直接修改”一项选中。也可以使用如下语句来实现：

use master
go
sp_configure 'allow updates', 1
go 
reconfigure with override
go

根据这个提示很快写出下面的更新代码：

sp_configure 'allow updates', 1
go
reconfigure with override
go
update syscolumns set colstat = 0 where id = OBJECT_ID('Test') and colstat = 1
go
update dbo.Test set Id = 3 where Id = 101
update dbo.Test set Id = 4 where Id = 102
go
update syscolumns set colstat = 1 where id = OBJECT_ID('Test') and name = 'Id'
go
dbcc checkident ('Test', RESEED, 4)
go
sp_configure 'allow updates', 0
go
reconfigure with override
go

事情总不是那么顺利，在数据库中执行后提示错误信息： Ad hoc updates to system catalogs are not allowed. 中文错误信息是： 不允许对系统目录进行即席更新。

继续研究发现，原来这个方法已经是很古老的技巧了。从SQL SERVER 2005和2008开始已经不再支持直接对系统表进行修改了。针对这种情况，使用笨方法，既然不能直接更新，那就只能删掉然后新增了。新增记录时，SQL SERVER刚好可以通过打开identity_insert插入指定的Id值。修改后的SQL语句如下：

dbcc checkident ('Test', RESEED, 0)
GO
set identity_insert dbo.Test on
GO
insert into dbo.Test(Id, Name) values(3, N'T3')
insert into dbo.Test(Id, Name) values(4, N'T4')
delete from dbo.Test where Id = 101
delete from dbo.Test where Id = 102
GO
set identity_insert dbo.Test off
GO
dbcc checkident ('Test', RESEED, 4)
GO

这样，我们就变相的实现了更新Id字段的目的。注意：这种方法只限于要修改的记录和其他表没有任何关联，如果涉及到外键约束，还需要更新所有的关联表，这就比较棘手了。不知道还有没有其他的解决方法，还望不吝赐教！

参考：

扫描二维码，在手机上阅读！

参考：

分类