如何在移动的数据库表上设置IDENTITY？ [英] How do I set IDENTITY on moved database tables?
问题描述
大家好，
我最近通过将脚本编写到文件然后运行该脚本，将Sql Server数据库从一台服务器移动到另一台服务器。一切都有效 - 一件事 - 身份。
在新服务器（SQL Server 2012或2014）上，我的表中省略了IDENTITY关键字（自动编号）。这意味着大多数表都无法插入新数据！
我知道一个解决方案是使用IDENTITY创建一个新列，比如customerID2（1 ，1）设置，然后删除旧的，并将新的重命名为customerID。但是，这种方法会使删除帖子的表格变得混乱。假设旧表的ID为1,2,5,6。在新列上使用IDENTITY，我会得到1,2,3,4等等，外键值将连接到错误的主键值。
如何解决这个问题？ （如果使用IDENTITY无法做到这一点我也可以使用其他解决方案。顺便说一下，这些表大约有30个表和200个存储过程。）
谢谢，祝新年快乐。
Petter
你可以使用 ALTER TABLE .. ALTER COLUMN（Transact-SQL） [ ^ ]但由于这些列可能在外键中使用，因此您将不得不放弃并在alter table语句的任一侧重新创建这些键。
您可以在SQL Server Manager中进行更改并让它生成必需的脚本。
Hi all,
I have recently moved a Sql Server db from one server to another by scripting it to file and then running that script. Everything works but one thing - IDENTITY.
On the new server (SQL Server 2012 or 2014), the IDENTITY keyword (auto numbering) is ommitted from my tables. This means that new data can't be inserted for most tables!
I know that one solution would be to create a new column, say customerID2, with IDENTITY(1,1) set, and then remove the old one and rename the new one to customerID. However, such an approach would mess things up in tables where posts have been deleted. Let's say that the old table has ID's 1, 2, 5, 6. With IDENTITY on the new column, I would get 1, 2, 3, 4, and so, the foreign key values would be connected to the wrong primary key values.
How do I solve this problem? (If it's not possible to do it using IDENTITY I am up for other solutions as well. BTW, there are about 30 tables and 200 stored procedures working with these tables.)
Thanks, and best wishes for the new year.
Petter
You can use the ALTER TABLE .. ALTER COLUMN (Transact-SQL)[^] but as these columns are probably used in foreign keys you will have to drop and recreate these keys either side of the alter table statement.
You might be able to make the change in SQL Server Manager and have it generate the requisite script.
这篇关于如何在移动的数据库表上设置IDENTITY？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
