How to remove identity on primary key column in SQL Server 2016

Question

How to remove identity on primary key column in SQL Server 2016?

Possible duplicate of [Remove Identity from a column in a table](https://stackoverflow.com/questions/8230257/remove-identity-from-a-column-in-a-table) — TZHX, May 22 '18 at 15:57

score 3 · Accepted Answer · answered May 22 '18 at 15:57

3

Create a new column, copy the data, drop original column

alter table yourtable
add newcolumn int

update yourtable
set newcolumn=oldcolumn

alter table yourtable
drop column oldcolumn

answered May 22 '18 at 15:57

Daniel Marcus

2,686
1
7
13

Thanks Daniel. But My identity column is a foreign key for other tables. – Naga May 22 '18 at 16:02
Remove all the foreign key constraints and then put them back when you are done - see the link above in the comments section for more detail – Daniel Marcus May 22 '18 at 16:03
Thank you Daniel. – Naga May 22 '18 at 16:49
How to turn off the identity on column and turn on the auto increament as column is referred as foreign key on other table? – Naga May 22 '18 at 16:50
Foreign key on the other table is the value that points to the primary key of another table - remove all foreign keys pointing the your identity column and then recreate them pointing to the new column when you are done – Daniel Marcus May 22 '18 at 16:51
Worked. Thank you. – Naga May 22 '18 at 18:36
Great - Please accept answer – Daniel Marcus May 22 '18 at 18:41

score -4 · Answer 2 · answered May 22 '18 at 16:03

you can't remove Identity on a primary key column. It is by default gets created when you choose a column as Primary. You need to do alternatives which I don't recommend on project tables.

1) Drop the primary key with cascade option (You will lose the links to child tables)

2) - You need to create another column with a different name in the same table. - copy that data into newly created column - Delete the primary key column.
this will leave orphan records in child tables that are being referenced.

This is incorrect, SQL Server primary key columns are not identity fields by default. — TabbyCool, Sep 04 '18 at 11:24

How to remove identity on primary key column in SQL Server 2016

2 Answers2