SQL 更改表以将 IDENTITY 设置为关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34629276/
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
Alter table to set the IDENTITY to off
提问by Kaja
I have a table, which has a column(orderid), which its IDENTITY is set to true. Now I would like to set it off. How can I do that with ALTER COLUMN?some thing like this?
我有一个表，它有一个列（orderid），它的 IDENTITY 设置为 true。现在我想把它关掉。我怎么能用 ALTER COLUMN 做到这一点？像这样的事情？
ALTER TABLE MyTable
ALTER Column MyColumn SET IDENTITY OFF
采纳答案by Rahul Tripathi
Once the identity column is set you cannot removeit or you cannot set it to OFF.
一旦设置了标识列，您就无法将其删除或将其设置为 OFF。
You probably have to drop the column by first copying the data into some other column(which does not have the identity). So it would be like add a new column to your table and copy the values of your existing identity column to it. Then drop the old column(having identity) and finally rename the new column to the old column name.
您可能必须首先将数据复制到其他一些列（没有标识）来删除该列。因此，这就像向表中添加一个新列并将现有标识列的值复制到其中。然后删除旧列（具有标识），最后将新列重命名为旧列名。
回答by StackUser
You have to use SET IDENTITY_INSERT TO ON. If you set it as ON then you should explicitly pass values to the ID Column.
您必须使用 SET IDENTITY_INSERT TO ON。如果您将其设置为 ON，那么您应该明确地将值传递给 ID 列。
Why should you switch off the Identity? May be you are trying to pass some explicit values.
为什么要关闭身份？可能是您试图传递一些明确的值。
Please refer the sample demo here.
请在此处参考示例演示。
-- Create tool table.
CREATE TABLE dbo.Tool
(
ID INT IDENTITY NOT NULL PRIMARY KEY,
NAME VARCHAR(40) NOT NULL
);
GO
-- Inserting values into products table.
INSERT INTO dbo.Tool
(NAME)
VALUES ('Screwdriver'),
('Hammer'),
('Saw'),
('Shovel');
GO
-- Create a gap in the identity values.
DELETE dbo.Tool
WHERE NAME = 'Saw';
GO
SELECT *
FROM dbo.Tool;
GO
-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Tool
(ID,
NAME)
VALUES (3,
'Garden shovel');
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON;
GO
-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool
(ID,
NAME)
VALUES (3,
'Garden shovel');
GO
SELECT *
FROM dbo.Tool;
GO
-- Drop products table.
DROP TABLE dbo.Tool;
GO
回答by Abdul Hannan Ijaz
You can do this in 4 steps
您可以通过 4 个步骤完成此操作
- Make new Column
- Copy Data to that Column
- Remove old column
- Rename new column to old one
- 创建新列
- 将数据复制到该列
- 删除旧列
- 将新列重命名为旧列
