Unable To Change Identity Specification To Yes In Sql Server Table

This may be a dumb question, but why can't I change the Identity Specification or (Is Identity) from 'No' to 'Yes'?

Solution 1:

Check your column data type.

If it is varchar then you can't change identity column.

To change identity column, it should have int data type.

Solution 2:

You cannot change the IDENTITY property of a column on an existing table. What you can do is add a new column with the IDENTITY property, delete the old column, and rename the new column with the old columns name.

Of course, what happens then (for some people) is that they don't like the fact that the new column appears "at the end" of the list of columns (even though you shouldn't care about the positions of columns - you should always be using their names). In that case, you need to do the same trick, but at a higher level - create a new table (with the column definition altered), copy data from the old table to the new, drop the old table, and rename the new one.

I thought SSMS still tried to pretend that it was possible, by doing the second trick behind the scenes.

Solution 3:

You can disable the Option "prevent saving changes that require table re-creation" but this is strongly unrecommended by msdn.

to do so go to:

Tools -> Options -> Designers There uncheck: "prevent saving changes that require table re-creation"

Solution 4:

Also check to make sure that there isn't a default value or binding on the column.

Solution 5:

Remove Default Value or Binding.