SQL修改表结构之添加主键，添加IDENTITY属性

设计一张表时没有考虑到主键Id及自增长，现又需要，原脚本：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ForbiddenType](
    [Id] [int] NOT NULL,
    [Type] [nvarchar](100) NOT NULL
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'屏蔽类型（0全部）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ForbiddenType', @level2type=N'COLUMN',@level2name=N'Type'
GO

 

并初始化了表数据：

INSERT [dbo].[ForbiddenType] ([Id], [Type]) VALUES (0, N'--All--')
INSERT [dbo].[ForbiddenType] ([Id], [Type]) VALUES (1, N'用户评论(客户端展示)')
INSERT [dbo].[ForbiddenType] ([Id], [Type]) VALUES (2, N'后台编辑(服务端)')

在此条件下实现后台操作Type与Type对应的值可编辑，要重新修改Id字段属性；

要成为IDENTITY标识列的列只能先被删除然后再添加同名列

----删除列
alter table tablename drop COLUMN id
GO
----添加IDENTITY列
alter table tablename add id int identity(1,1)
GO
----设置IDENTITY列为主键
alter table tablename add constraint [PK_tablename] PRIMARY KEY CLUSTERED ([id])

但是这样做会改变主键字段在表中的顺序

如果不想改变表中主键的位置，有两种思路，一是删除表后重建；一是不删除，将字段逐个删除再依次添加字段：

看代码：

--修改表ForbiddenType
IF EXISTS (
       SELECT *
       FROM   dbo.sysobjects
       WHERE  id = OBJECT_ID(N'[dbo].[ForbiddenType]')
              AND OBJECTPROPERTY(id ,N'IsUserTable') = 1
   )
    DROP TABLE [dbo].[ForbiddenType]
 GO   
CREATE TABLE [dbo].[ForbiddenType]
(
    [Id]         [int] NOT NULL IDENTITY(1 ,1)
   ,[TypeId]     INT NOT NULL
   ,[Type]       [nvarchar](100) NOT NULL
   ,
) ON [PRIMARY]
    -- TypeId字段说明
EXEC sys.sp_addextendedproperty @name=N'MS_Description'
    ,@value=N'屏蔽类型对应值(类型对应Id)'
    ,@level0type=N'SCHEMA'
    ,@level0name=N'dbo'
    ,@level1type=N'TABLE'
    ,@level1name=N'ForbiddenType'
    ,@level2type=N'COLUMN'
    ,@level2name=N'TypeId' 
    -- Type字段说明
EXEC sys.sp_addextendedproperty @name=N'MS_Description'
    ,@value=N'屏蔽类型'
    ,@level0type=N'SCHEMA'
    ,@level0name=N'dbo'
    ,@level1type=N'TABLE'
    ,@level1name=N'ForbiddenType'
    ,@level2type=N'COLUMN'
    ,@level2name=N'Type'

INSERT INTO [dbo].[ForbiddenType]
  (
    TypeId
   ,TYPE
  )
VALUES
  (
    1
   ,'用户评论(客户端展示)'
  )
INSERT INTO [dbo].[ForbiddenType]
  (
    TypeId
   ,TYPE
  )
VALUES
  (
    2
   ,'后台编辑(服务端)'
  )
GO

 

TRUNCATE TABLE ForbiddenType  --清空数据库表数据
                              --删除表中列Type的描述属性：   
EXEC sp_dropextendedproperty 'MS_Description',
     'SCHEMA',
     dbo,
     'TABLE',
     'ForbiddenType',
     'COLUMN',
     TYPE   
GO
ALTER TABLE ForbiddenType DROP COLUMN Id
GO
--添加自增的Id列并设为主键
ALTER TABLE ForbiddenType  ADD [IDs] [int] NOT NULL IDENTITY(1, 1) 
EXEC sp_rename 'ForbiddenType.[IDs]',
     'Id',
     'COLUMN'; 
ALTER TABLE ForbiddenType ADD CONSTRAINT [PK_ForbiddenType] PRIMARY KEY 
CLUSTERED([Id]) 
ALTER TABLE ForbiddenType  ADD [TypeId] [int] NOT NULL
GO
 
ALTER TABLE ForbiddenType DROP COLUMN TYPE
ALTER TABLE ForbiddenType  ADD TYPE [nvarchar](100) NOT NULL
GO 

EXEC sys.sp_addextendedproperty @name = N'MS_Description',
     @value = N'屏蔽类型对应值(类型对应Id)',
     @level0type = N'SCHEMA',
     @level0name = N'dbo',
     @level1type = N'TABLE',
     @level1name = N'ForbiddenType',
     @level2type = N'COLUMN',
     @level2name = N'TypeId' 
    -- Type字段说明
EXEC sys.sp_addextendedproperty @name = N'MS_Description',
     @value = N'屏蔽类型',
     @level0type = N'SCHEMA',
     @level0name = N'dbo',
     @level1type = N'TABLE',
     @level1name = N'ForbiddenType',
     @level2type = N'COLUMN',
     @level2name = N'Type'
    GO
INSERT INTO [dbo].[ForbiddenType]
  (
    TypeId,
    TYPE
  )
VALUES
  (
    1,
    '用户评论(客户端展示)'
  )
INSERT INTO [dbo].[ForbiddenType]
  (
    TypeId,
    TYPE
  )
VALUES
  (
    2,
    '后台编辑(服务端)'
  )

 

 

对于一张表,想要添加与删除主键,可参见下面

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主键名]') and OBJECTPROPERTY(object_id(N'[列名]'), N'IsPrimaryKey') = 1)
ALTER  TABLE 表名 DROP  CONSTRAINT [主键名]
ALTER  TABLE 表名 ADD   CONSTRAINT [新主键名] PRIMARY   KEY  ([列名])

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK_orders]') and OBJECTPROPERTY(OBJECT_ID(N'dbo.[orders].[pi_no]'), N'IsPrimaryKey') = 1)
ALTER  TABLE orders DROP  CONSTRAINT [PK_orders]
ALTER  TABLE orders ADD   CONSTRAINT [PK_orders] PRIMARY KEY  ([form_no]) ON  [PRIMARY]