SQL Server中为现有的列添加或去掉标识（Identity）属性

SQL Server中为现有的列添加或去掉标识属性

 

问题：

　　SQLServer有一个不错的常用特性，就是标识列。利用这个特性，你可以轻松地在表里为每一行创建唯一的值。添加一个新的列并设置为标识列，或者删除现有标识列都很简单，但是如果是要把一个现有的列改为标识列，或者去掉现有的标识列的标识属性，那应该怎么做呢?

　　

解答：

　　要做到这一步并不容易，或者说没有什么简单易行的方法。SQL Server的设计里并没有很简单的就现有的列加上或去掉标识属性的方法。最安全的办法是，创建一个新的列并设置为标识列，或者创建一个新的表，然后把数据移植进去。

　　我们来看看一些例子：

　　例一

　　现有一个简单的表，表里含有两列，其中一列为标识列。

CREATE TABLE [dbo].[Test1](    [id] [int] IDENTITY(1,1) NOT NULL,    [name] [nchar](10) NULL )

　　如果我们用SQL Server Management Studio来去掉”id”列的标识值，这样就会创建出一个新的临时表，原表的数据都移到了临时表中，然后删除原表并为新表重命名。我们可以从下面的脚本里看到这个过程。

　　要看到这个脚本，只要使用 Management Studio来进行改动，然后右击designer，选择"Generate Change Script"。

以下是引用片段： /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_Test1    (    id INT NOT NULL,    name NCHAR(10) NULL    )  ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.Test1)     EXEC("INSERT INTO dbo.Tmp_Test1 (id, name)       SELECT id, name FROM dbo.Test1 WITH (HOLDLOCK TABLOCKX)") GO DROP TABLE dbo.Test1 GO EXECUTE sp_rename N"dbo.Tmp_Test1", N"Test1", "OBJECT"  GO COMMIT

　　例二

　　我们现在来把它弄得更复杂一点，在表里设置主键，然后创建以外键约束的另一个表，以第一个表主键为索引。我们可以看到这样做需要花更多工夫。

以下是引用片段： CREATE TABLE [dbo].[Test1](    [id] [int] IDENTITY(1,1) NOT NULL,    [name] [nchar](10) NULL,  CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED  (    [id] ASC )) GO CREATE TABLE [dbo].[Test2](    [id] [int] NULL,    [name2] [nchar](10) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Test2] WITH CHECK ADD CONSTRAINT [FK_Test2_Test1] FOREIGN KEY([id]) REFERENCES [dbo].[Test1] ([id]) GO ALTER TABLE [dbo].[Test2] CHECK CONSTRAINT [FK_Test2_Test1] GO

　　如果我们按照上述的方法，然后使用Management Studio去掉表“test 1”中“id”列的标识值，可以从脚本里看到，这样需要更多的步骤才能达到我们的目的。

　　首先创建一个列属性正确的临时表“Tmp_Test1”

　　把数据从表“Test1”移植到临时表“Tmp_Test1”中

　　去掉Test2的外键约束

　　删除表“Test1”

　　临时表“Tmp_Test1”重命名为“Test1”

　　在表“Test1”里创建主键

　　最后重新在表“Test2”里创建外键约束。步骤太多了!

以下是引用片段： /* To prevent any potential data loss issues, you should review this  script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_Test1    (    id INT NOT NULL,    name NCHAR(10) NULL    )  ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.Test1)     EXEC("INSERT INTO dbo.Tmp_Test1 (id, name)       SELECT id, name FROM dbo.Test1 WITH (HOLDLOCK TABLOCKX)") GO ALTER TABLE dbo.Test2    DROP CONSTRAINT FK_Test2_Test1 GO DROP TABLE dbo.Test1 GO EXECUTE sp_rename N"dbo.Tmp_Test1", N"Test1", "OBJECT"  GO ALTER TABLE dbo.Test1 ADD CONSTRAINT    PK_Test1 PRIMARY KEY CLUSTERED     (    id    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo.Test2 ADD CONSTRAINT    FK_Test2_Test1 FOREIGN KEY    (    id    ) REFERENCES dbo.Test1    (    id    ) ON UPDATE  NO ACTION      ON DELETE  NO ACTION      GO COMMIT

　　同样，如果我们想要对现有的列进行修改，把其中一列变成标识列的话，需要花费同样的工夫。

　 　另外一种可行的方法就是增加一列并设置为标识列，或者增加一列然后把旧列的数据移植到新列里。然后你可以删除之前的列，使用sp_rename存储过程 来对进行新增列重命名。如果这些列里含有索引、外键或其他约束，你需要把它们都去掉才能作上述的修改。所以，这个方法也快不了多少。

　　我们现在知道，要修改现有列的标识属性没有什么捷径可走。你也可以在网络上搜索到其他的办法，可以修改系统表里的值。这些方法确实可以行得通，不过要注意的是，一旦出了什么差错，你可能会把数据全毁掉。

　　对于比较小的数据库系统而言，这里介绍的方法没有什么太大问题。不过对于忙碌的大数据库系统来说，去掉表里的约束、修改表里的标识属性就会是一件让人头疼的事情了。现在只能是更高版本的SQL里会不会出现什么让人眼前一亮的新特性了。