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 (开发者_JAVA百科Is Identity) from 'No' to 'Yes'?

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.

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"

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.

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

Remove Default Value or Binding.

Following on from Damien_The_Unbeliever's comment above, you can edit the column order in Visual Studio's design interface.

I've managed to do this successfully. To confirm, the steps are:

1. Add a new column with the IDENTITY property
2. delete the old column,
3. Rename the new column with the old columns name.
4. (Optional) Re-order columns to have the IDENTITY column from Visual Studio.

The solution that worked for me was to change the Data Type to int from its default nchar. Additionally, I had to refresh my database server connection to get it working.

As you can see the yes value for the identity specification is disabled so the other answers above is not applicable for me but following Joy's answer, I saw that I really had a Default Value or Binding. I just omitted the default value and everything i neede was enabled.

Took me a while to find the solution to this, at first I was trying to find a way to change a value from here (it's yes cause I was able to change it using the next way)

But then by right-clicking -> properties -> Table designer -> Choose Identity column

Doing this made the "Identity Specifiacation" to "Yes"

I think Firstly you have to check the (isIdentity) checkbox after that you can able to check the Identity Specification Checkbox