我需要将表的主键更改为标识列，而且表中已经有了许多行。
我有一个脚本来清理ID以确保它们从1开始连续，测试数据库上运行良好。
如何使用SQL命令更改该列以具有标识属性？
21个回答
0
这个答案与这个答案非常相似，只有一些细微的差别。
例如，你有3个像这样的表，其中有两个外键。
DDL：
如果你想给
例如，你有3个像这样的表，其中有两个外键。
DDL：
CREATE TABLE [Table_1]
(
[id] [int] NOT NULL PRIMARY KEY,
[Name] [varchar](10) NULL
)
GO
CREATE UNIQUE NONCLUSTERED INDEX Table_1_Index
ON [Table_1] ([Name]);
CREATE TABLE [Table_2]
(
[id] [int] NOT NULL
FOREIGN KEY REFERENCES [Table_1]([id]) ON UPDATE CASCADE,
[car] [varchar](10) NULL
)
GO
CREATE TABLE [Table_3]
(
[id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Name] [varchar](10) NULL
FOREIGN KEY REFERENCES [Table_1]([Name])
)
GO
INSERT INTO [Table_1]
VALUES (10000, 'John'), (10001, 'Mike')
INSERT INTO [Table_2]
VALUES (10000, 'Benz'), (10000, 'Volvo'), (10001, 'BMW')
INSERT INTO [Table_3]
VALUES ('John')
GO
如果你想给
Table_1 中的 id 列添加身份，并且想从10000开始，每次递增1，运行以下查询：DECLARE @Schema SysName = 'dbo'
DECLARE @TableName SysName = 'Table_1'
DECLARE @ColumnName SysName = 'id'
DECLARE @IdentityType VARCHAR(20) = 'IDENTITY(10000,1)'
DECLARE @Table_Name SYSNAME = QUOTENAME(@Schema) + '.' + QUOTENAME(@TableName)
DECLARE @SQLCreateTable NVARCHAR(MAX)
DECLARE @SQLFK NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
--get foreign keys
;WITH fk_columns AS
(
SELECT
constraint_object_id,
OBJECT_NAME(constraint_object_id) KeyName,
OBJECT_NAME(fkc.parent_object_id) FKTable,
OBJECT_NAME(fkc.referenced_object_id) PKTable,
COL_NAME(fkc.parent_object_id, parent_column_id) FKColumn,
COL_NAME(fkc.referenced_object_id, referenced_column_id) PKColumn,
delete_referential_action,
update_referential_action,
is_not_trusted
FROM
sys.foreign_key_columns fkc
JOIN
sys.foreign_keys fk ON fk.object_id = fkc.constraint_object_id
WHERE
fkc.referenced_object_id = OBJECT_ID(@Table_Name)
)
SELECT
@SQLFK = ISNULL((SELECT (
SELECT CHAR(13) +
'ALTER TABLE ' + fk.FKTable + ' WITH'
+ CASE WHEN fk.is_not_trusted = 1
THEN ' NOCHECK'
ELSE ' CHECK'
END +
' ADD CONSTRAINT [' + fk.KeyName + '] FOREIGN KEY('
+ STUFF((
SELECT
', [' + k.FKColumn + ']'
FROM
fk_columns k
WHERE
k.constraint_object_id = fk.constraint_object_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')' +
' REFERENCES '+@Table_Name+' ('
+ STUFF((
SELECT
', [' + k.PKColumn + ']'
FROM
fk_columns k
WHERE
k.constraint_object_id = fk.constraint_object_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')'
+ CASE
WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'
WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'
ELSE ''
END
+ CASE
WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'
ELSE ''
END
+ CHAR(13) + 'ALTER TABLE ' + fk.FKTable + ' CHECK CONSTRAINT [' + fk.KeyName + ']' + CHAR(13)
FROM fk_columns fk WITH (NOWAIT)
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
--drop foreign keys
;WITH fk_columns AS
(
SELECT
OBJECT_NAME(constraint_object_id) KeyName,
OBJECT_NAME(fkc.parent_object_id) FKTable
FROM
sys.foreign_key_columns fkc
JOIN
sys.foreign_keys fk ON fk.object_id = fkc.constraint_object_id
WHERE
fkc.referenced_object_id = OBJECT_ID(@Table_Name)
)
SELECT @SQL = ISNULL((SELECT (
SELECT
CHAR(13) + 'ALTER TABLE ' + fk.FKTable +
' DROP CONSTRAINT [' + fk.KeyName + ']' + CHAR(13)
FROM
fk_columns fk WITH (NOWAIT)
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
EXECUTE sp_executesql @SQL
--find not exist table name
DECLARE @i INT = 1
WHILE OBJECT_ID(QUOTENAME(@Schema) + '.' +QUOTENAME(@TableName) + '_Temp' + CAST(@i AS varchar), 'U') IS NOT NULL
SET @i += 1
DECLARE @TempName SysName = @TableName + '_Temp' + CAST(@i AS VARCHAR)
DECLARE @Temp_Name SysName = QUOTENAME(@Schema) + '.' +QUOTENAME(@TableName + '_Temp' + CAST(@i AS VARCHAR))
--create temp table like table code
DECLARE @object_id INT = OBJECT_ID(@Table_Name)
;WITH index_column AS
(
SELECT
ic.[object_id],
ic.index_id,
ic.is_descending_key,
ic.is_included_column,
c.name
FROM
sys.index_columns ic WITH (NOWAIT)
JOIN
sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
WHERE
ic.[object_id] = @object_id
),
fk_columns AS
(
SELECT
k.constraint_object_id,
cname = c.name,
rcname = rc.name
FROM
sys.foreign_key_columns k WITH (NOWAIT)
JOIN
sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id
JOIN
sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
WHERE
k.parent_object_id = @object_id
)
SELECT @SQLCreateTable = 'CREATE TABLE ' + @Temp_Name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT
CHAR(9) + ', [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + cc.[definition]
ELSE UPPER(tp.name) +
CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name IN ('decimal', 'numeric')
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
CASE WHEN c.name = @ColumnName THEN ' ' +@IdentityType ELSE '' END +
CASE WHEN c.is_nullable = 1 and c.name <> @ColumnName THEN ' NULL' ELSE ' NOT NULL' END +
CASE WHEN dc.[definition] IS NOT NULL and c.name <> @ColumnName THEN ' DEFAULT' + dc.[definition] ELSE '' END
END + CHAR(13)
FROM
sys.columns c WITH (NOWAIT)
JOIN
sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
LEFT JOIN
sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
LEFT JOIN
sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
LEFT JOIN
sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE
c.[object_id] = @object_id
ORDER BY
c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
+ ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +
(SELECT STUFF((
SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
FROM
sys.index_columns ic WITH (NOWAIT)
JOIN
sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE
ic.is_included_column = 0
AND ic.[object_id] = k.parent_object_id
AND ic.index_id = k.unique_index_id
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))
+ ')' + CHAR(13)
FROM
sys.key_constraints k WITH (NOWAIT)
WHERE
k.parent_object_id = @object_id
AND k.[type] = 'PK'), '') + ')' + CHAR(13)
+ ISNULL((SELECT (
SELECT CHAR(13) +
'ALTER TABLE ' + @Temp_Name + ' WITH'
+ CASE WHEN fk.is_not_trusted = 1
THEN ' NOCHECK'
ELSE ' CHECK'
END +
' ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY('
+ STUFF((
SELECT
', [' + k.cname + ']'
FROM
fk_columns k
WHERE
k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')' +
' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('
+ STUFF((
SELECT
', [' + k.rcname + ']'
FROM
fk_columns k
WHERE
k.constraint_object_id = fk.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
+ ')'
+ CASE
WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'
WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'
WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'
ELSE ''
END
+ CASE
WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'
WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'
WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'
ELSE ''
END
+ CHAR(13) + 'ALTER TABLE ' + @Temp_Name + ' CHECK CONSTRAINT [' + fk.name + ']' + CHAR(13)
FROM sys.foreign_keys fk WITH (NOWAIT)
JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
WHERE fk.parent_object_id = @object_id
FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')
+ ISNULL(((SELECT
CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END
+ CASE i.type WHEN 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED' END
+ ' INDEX [' + i.name + '] ON ' + @Temp_Name + ' (' +
STUFF((
SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
FROM
index_column c
WHERE
c.is_included_column = 0
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
+ ISNULL(CHAR(13) + 'INCLUDE (' +
STUFF((
SELECT
', [' + c.name + ']'
FROM
index_column c
WHERE
c.is_included_column = 1
AND c.index_id = i.index_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + CHAR(13)
FROM
sys.indexes i WITH (NOWAIT)
WHERE
i.[object_id] = @object_id
AND i.is_primary_key = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
), '')
--rename primary key constaint on column from table
DECLARE @PrimaryName SYSNAME
SELECT @PrimaryName=i.name
FROM
sys.index_columns ic WITH (NOWAIT)
JOIN
sys.indexes i WITH (NOWAIT) ON i.object_id = ic.object_id and i.index_id = ic.index_id
WHERE
i.object_id = OBJECT_ID(@Table_Name) and Col_Name(ic.object_id, column_id) = @ColumnName and is_primary_key = 1
SET @SQL = N'EXEC sp_rename ' + @PrimaryName + ', ' + 'ThisMustNotBeExist'+ @PrimaryName + ', N''OBJECT'''
EXECUTE sp_executesql @SQL
--rename index on column from table
DECLARE @IndexName SYSNAME
SELECT
@IndexName = i.name
FROM
sys.index_columns ic WITH (NOWAIT)
JOIN
sys.indexes i WITH (NOWAIT) ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE
i.object_id = OBJECT_ID(@Table_Name)
AND Col_Name(ic.object_id, column_id) = @ColumnName
AND is_primary_key = 0
SET @SQL = N'EXEC sp_rename ' + @Table_Name + '.' + @IndexName + ', ' + 'ThisMustNotBeExist'+ @IndexName + ', N''INDEX'''
EXECUTE sp_executesql @SQL
--run create temp table like table code
EXECUTE sp_executesql @SQLCreateTable
--Move table data to temp data
SET @SQL = N'ALTER TABLE '+ @Table_Name + ' SWITCH TO ' + @Temp_Name
EXECUTE sp_executesql @SQL
--check clean up
SET @SQL = N'IF EXISTS (SELECT * FROM '+ @Table_Name + ') THROW 50000, ''Table is not clear'', 1'
EXECUTE sp_executesql @SQL
--drop table
SET @SQL = N'DROP TABLE '+ @Table_Name
EXECUTE sp_executesql @SQL
--rename temp table to table
EXEC sys.sp_rename @Temp_Name, @TableName, 'OBJECT';
--create foreign keys
EXECUTE sp_executesql @SQLFK
DBCC CHECKIDENT(@TableName, RESEED)
注意
- 此代码仅复制键和索引，如果您在该表上有其他内容，在运行此代码后将丢失它们
如果您还想复制表的触发器或扩展属性，请查看this answer并在执行之前将@SQL2和@SQL3添加到@SQLCreateTable中
- AliReza
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接