SQL 如何从sql server 2012中的表中删除自动增量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23511309/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to remove auto increment from table in sql server 2012
提问by user3458120
I have created a table in SQL Server 2012 with primary key as auto increment. But how can I remove that auto increment property from the table using a SQL query?
我在 SQL Server 2012 中创建了一个表，主键作为自动增量。但是如何使用 SQL 查询从表中删除该自动增量属性？
回答by Steve Pettifer
If you need to keep the data in that column then create a new column on the table which is of the same type (but a different name), copy the data from the column you want to get rid of to the new one, drop the old column and rename the new. Complete example:
如果您需要保留该列中的数据，然后在表上创建一个相同类型（但名称不同）的新列，将数据从要删除的列复制到新列，删除旧列并重命名新列。完整示例：
CREATE TABLE test(col1 INT IDENTITY (1,1) NOT NULL, col2 VARCHAR(10) NULL);
ALTER TABLE test ADD col3 INT NULL;
UPDATE test SET col3 = col1;
ALTER TABLE test DROP COLUMN col1;
EXEC sp_rename 'dbo.test.col3', 'col1', 'COLUMN';
回答by Avi Turner
The easiest way would be:
最简单的方法是：
- Open
SQL Server Management Studio. - Locate Server > DataBase > Table.
- Right Click on the Table > Select Design.
- In the design window, Highlight the column you want to modify.
- In the
Column PropertiesWindow browse toIdentity Specification>Is IdentityAnd set toNo. - Go to the toolbar menu >
Table Designer> SelectGenerate Change Script... - Walla, you got the requested script.
- 打开
SQL Server Management Studio。 - 找到服务器 > 数据库 > 表。
- 右键单击表格 > 选择设计。
- 在设计窗口中，突出显示要修改的列。
- 在
Column Properties窗口中浏览到Identity Specification>Is Identity并设置为No。 - 转到工具栏菜单 >
Table Designer> 选择Generate Change Script... - 瓦拉，你得到了请求的脚本。
I Like using this method for getting scripts, since it allows me to generate scripts I'm not sure how to compose from scratch and thus learning and improving my skills...
我喜欢使用这种方法来获取脚本，因为它允许我生成脚本我不知道如何从头开始编写，从而学习和提高我的技能......
回答by dean
If it's a primary key column, then you have to drop the PK first. If there's any tables referencing it, then you'll have to drop these FKs to be able to drop the PK. After that, add another column of the same type, update it with values from identity column, drop the identity column, rename the new column to whatever the name of identity column was (with sp_renameprocedure), recreate the PK, recreate the FKs, check if everything went right.
如果是主键列，则必须先删除PK。如果有任何表引用它，那么您必须删除这些 FK 才能删除 PK。之后，添加相同类型的另一列，使用身份列中的值更新它，删除身份列，将新列重命名为身份列的名称（使用sp_rename过程），重新创建 PK，重新创建 FK，检查如果一切顺利。
I'd be very careful doing it on a production database. Ensure that noone can access the data while you're doing this.
我会非常小心地在生产数据库上做这件事。确保在您执行此操作时没有人可以访问数据。
回答by Chris Sim
I searched a lot to find a simple solution to remove the auto increment because i should do a lot of work if i drop the column and the primary key which was a foreign key on another table where I should remove the data that are using my foreign ... finally I ended up with a very simple solution that made my life easy:
我搜索了很多找到一个简单的解决方案来删除自动增量因为如果我删除列和主键是另一个表上的外键我应该做很多工作我应该删除使用我的外键的数据...最后我得到了一个非常简单的解决方案，让我的生活变得轻松：
SET IDENTITY_INSERT <table_name> ON ;
回答by Goutham Arul
SET IDENTITY_INSERT [TABLE] OFF.. this allows to remove the auto increment to off state.., so that we have to enter the value in thatcolumn
SET IDENTITY_INSERT [TABLE] OFF.. 这允许删除自动增量到关闭状态..，因此我们必须在该列中输入值
