Clever Geek Handbook

Updating column id in active table

I have a live (ie used by my users) Table with a column "some_id" that was never set to "Is Identity" when the table was created. Now I have 2000 rows where some_id is NULL.

Can I change the identity specification now and is it possible that something breaks for my users?

Do I have to first update each "some_id" row with a prepared statement so that the first record of some_id will be 1, the second will be 2, etc., and then set it to Identity? (I have a unique date column)

+3


source to share


1 answer


You cannot modify an existing column and add identity()

, but you can delete an existing column and add a new column with identity()

. And it will auto populate.



alter table t drop column some_id;
alter table t add some_id int not null identity(1,1);
+2


source






More articles:


All Articles