设计一张表时没有考虑到主键Id及自增长，现又需要，原脚本：
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[ForbiddenType]( [Id] [int] NOT NULL, [Type] [nvarchar](100) NOT NULL) ON [PRIMARY]GOEXEC 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 idGO----添加IDENTITY列alter table tablename add id int identity(1,1)GO----设置IDENTITY列为主键alter table tablename add constraint [PK_tablename] PRIMARY KEY CLUSTERED ([id])
但是这样做会改变主键字段在表中的顺序
如果不想改变表中主键的位置，有两种思路，一是删除表后重建；一是不删除，将字段逐个删除再依次添加字段：
看代码：
--修改表ForbiddenTypeIF 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 GOALTER TABLE ForbiddenType DROP COLUMN IdGO--添加自增的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 NULLGO ALTER TABLE ForbiddenType DROP COLUMN TYPEALTER TABLE ForbiddenType ADD TYPE [nvarchar](100) NOT NULLGO 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' GOINSERT 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]