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

时间:2021-12-20 16:01:52

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

SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO CREATE TABLE [dbo].[ForbiddenType](
[Id] [int] NOT NULL,
[Type] [nvarchar]() 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 (, N'--All--')
INSERT [dbo].[ForbiddenType] ([Id], [Type]) VALUES (, N'用户评论(客户端展示)')
INSERT [dbo].[ForbiddenType] ([Id], [Type]) VALUES (, N'后台编辑(服务端)')

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

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

----删除列
alter table tablename drop COLUMN id
GO
----添加IDENTITY列
alter table tablename add id int identity(,)
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') =
)
DROP TABLE [dbo].[ForbiddenType]
GO
CREATE TABLE [dbo].[ForbiddenType]
(
[Id] [int] NOT NULL IDENTITY( ,)
,[TypeId] INT NOT NULL
,[Type] [nvarchar]() 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
( ,'用户评论(客户端展示)'
)
INSERT INTO [dbo].[ForbiddenType]
(
TypeId
,TYPE
)
VALUES
( ,'后台编辑(服务端)'
)
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(, )
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]() 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
(
,
'用户评论(客户端展示)'
)
INSERT INTO [dbo].[ForbiddenType]
(
TypeId,
TYPE
)
VALUES
(
,
'后台编辑(服务端)'
)

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[主键名]') and OBJECTPROPERTY(object_id(N'[列名]'), N'IsPrimaryKey') = )
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') = )
ALTER TABLE orders DROP CONSTRAINT [PK_orders]
ALTER TABLE orders ADD CONSTRAINT [PK_orders] PRIMARY KEY ([form_no]) ON [PRIMARY]