Geek Answers Handbook

Modify the table to set IDENTITY

I have a table in which there is a column (orderid) for which IDENTITY is set to true. Now I would like to disable it. How can I do this with ALTER COLUMN? Something like that?

ALTER TABLE MyTable ALTER Column MyColumn SET IDENTITY OFF
+6
source share
3 answers

Once the identifier column is set, you cannot delete it or you cannot set it to OFF.

You may have to delete the column by first copying the data to another column (which does not have an identifier). This way it will be like adding a new column to your table and copying the values ​​of your existing identity column onto it. Then discard the old column (with identity) and finally rename the new column to the name of the old column.

+6
source

You must use SET IDENTITY_INSERT TO ON. If you set it to ON, you must explicitly pass the values ​​to the ID column.

Why should you disable Identity? Maybe you are trying to pass some explicit values.

Please refer to the 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
+6
source

You can do it in 4 steps

  • Create a new column
  • Copy data to this column
  • Delete old column
  • Rename the new column to the old
+4
source

More articles:


All Articles