SQL Server数据库中为现有的列添加或去掉标识(Identity)属性
小标 2018-07-06 来源 : 阅读 18 评论 0

摘要:本文主要向大家介绍了SQL Server数据库中为现有的列添加或去掉标识(Identity)属性,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。

本文主要向大家介绍了SQL Server数据库中为现有的列添加或去掉标识(Identity)属性,通过具体的内容向大家展现,希望对大家学习SQLServer数据库有所帮助。

 

问题:

  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里会不会出现什么让人眼前一亮的新特性了。

本文由职坐标整理并发布,希望对同学们学习SQL Server有所帮助,更多内容请关注职坐标数据库SQL Server数据库频道!

本文由 @小标 发布于职坐标。未经许可,禁止转载。
喜欢 | 0 不喜欢 | 0
看完这篇文章有何感觉?已经有0人表态,0%的人喜欢 快给朋友分享吧~
评论(0)
后参与评论
X
免费获取海同IT培训资料
验证码手机号,获得海同独家IT培训资料
获取验证码
提交

版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号