我需要将一个表的主键更改为一个标识列,并且在表中已经有许多行。
我有一个脚本来清理id,以确保它们从1开始是顺序的,在我的测试数据库上运行良好。
更改列以具有标识属性的SQL命令是什么?
我需要将一个表的主键更改为一个标识列,并且在表中已经有许多行。
我有一个脚本来清理id,以确保它们从1开始是顺序的,在我的测试数据库上运行良好。
更改列以具有标识属性的SQL命令是什么?
当前回答
根据设计,没有简单的方法来打开或关闭现有列的标识特性。要做到这一点,唯一干净的方法是创建一个新列并使其成为标识列,或者创建一个新表并迁移数据。
如果我们使用SQL Server Management Studio去除列“id”上的标识值,则会创建一个新的临时表,数据被移动到临时表中,旧表被删除,新表被重命名。
使用Management Studio进行更改,然后在设计器中右键单击并选择“生成更改脚本”。
你会看到这就是SQL server在后台所做的。
其他回答
您不能更改现有列的标识。
你有两个选择,
创建一个带有标识的新表并删除现有表 创建一个具有标识的新列并删除现有列
方法1。(新表)在这里,您可以保留新创建的标识列上的现有数据值。请注意,如果不满足'if not exists',您将丢失所有数据,因此请确保您将条件也放在drop上!
CREATE TABLE dbo.Tmp_Names
(
Id int NOT NULL
IDENTITY(1, 1),
Name varchar(50) NULL
)
ON [PRIMARY]
go
SET IDENTITY_INSERT dbo.Tmp_Names ON
go
IF EXISTS ( SELECT *
FROM dbo.Names )
INSERT INTO dbo.Tmp_Names ( Id, Name )
SELECT Id,
Name
FROM dbo.Names TABLOCKX
go
SET IDENTITY_INSERT dbo.Tmp_Names OFF
go
DROP TABLE dbo.Names
go
Exec sp_rename 'Tmp_Names', 'Names'
方法2(新列)不能在新创建的标识列上保留现有数据值,标识列将保留数字序列。
Alter Table Names
Add Id_new Int Identity(1, 1)
Go
Alter Table Names Drop Column ID
Go
Exec sp_rename 'Names.Id_new', 'ID', 'Column'
请参阅以下Microsoft SQL Server论坛帖子了解更多细节:
如何将列更改为identity(1,1)
不能将列更改为IDENTITY列。您需要做的是创建一个新列,从一开始就定义为IDENTITY,然后删除旧列,并将新列重命名为旧名称。
ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)
ALTER TABLE (yourTable) DROP COLUMN OldColumnName
EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'
Marc
在对象资源管理器中右键单击表名。你会得到一些选择。点击“设计”。将为该表打开一个新选项卡。您可以在“列属性”中添加标识约束。
我不相信您可以使用tsql将现有列更改为标识列。但是,您可以通过Enterprise Manager设计视图来完成。
或者,您可以创建一个新行作为标识列,删除旧列,然后重命名新列。
ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY CLUSTERED
我是一名java开发人员,碰巧加入了一个没有DBA的团队,而作为一名开发人员,我无法获得DBA权限。我的任务是在两个数据库之间移动整个模式,所以没有DBA,我必须通过运行脚本来完成,不能使用SQL Server 2008中的GUI,因为我没有管理权限。
但是,在新模式上运行存储过程时,所有内容都被移动了。表,我发现我丢失了一个表的标识字段。我仔细检查了创建表的脚本,它在那里,但是,SQL Server在我运行脚本时没有得到它。后来,一位DBA告诉我,他以前见过同样的问题。
在任何情况下,对于SQL Server 2008,这些是我采取的步骤来解决这个问题,他们工作,所以我张贴在这里,希望它会对某人有所帮助。这是我所做的,因为我对另一个表有FK依赖,这使得这更困难:
我使用这个查询来验证标识确实缺失,并查看表上的依赖关系。
1)。查找表上的统计信息:
exec sp_help 'dbo.table_name_old';
2)。创建一个重复的、完全相同的新表,只是在原来的PK字段上添加了一个标识字段。
3)。禁用标识以移动数据。
SET IDENTITY_INSERT dbo.table_name ON
4)。传输数据。
INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT
field1, field2, etc...
FROM
dbo.table_name_old;
5)。验证数据是否存在。
SELECT * FROM dbo.table_name_new
6)。重新启用标识。
SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF
7)。这是我找到的获得所有FK关系以验证原始表引用哪个表作为依赖项的最佳脚本 我遇到过很多,所以它是一个保存者!
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
ORDER BY ReferenceTableName;
8)。在进行下一步之前,请确保您拥有所有涉及到的表的所有PK和FK脚本。
9)。您可以右键单击每个键并使用SQL Server 2008编写脚本
10)。使用以下语法从依赖表中删除FK:
ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]
11)。删除原来的表:
DROP TABLE dbo.table_name_old;
13)。接下来的步骤依赖于您在SQL Server 2008第9步中创建的脚本。
将PK添加到新表中。
——将FK添加到新表中。
——将FK添加回依赖项表。
14)。验证所有内容是否正确和完整。我使用GUI查看表格。
15)。将新表重命名为原始表名。
exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';
最后,一切都成功了!