how to make an existing table field auto-increment in MsSQL

Tangara G

10y894

hi,

I am using Ms SQL 2014.

Please find below the table I have created.

My problem is that I do not know how to make my primary key auto-increment from here.

There isn't a script file for me to load, to my surprise.

Another thing is that I have 2 copies of MS SQL - one is the SQLExpress and the other one is the main one. Not sure why it is so.

Hope someone can advise me on the above. Tks.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp10y ago
Accepted answer
Hi, there's nothing wrong with your mssql. It is a common warning you are getting. Just follow below link to find out the work around -
https://support.microsoft.com/en-in/kb/956176
Nitin Sontakke10y ago
Go to Tools -> Options - Designers
And uncheck "Prevent saving changes that require table re-creation" and you should be okay.
This to protect accidental table drop and create in production (or any other, for that matter) environments.
Please google the term to understand the full impact of unchecking that option and please do it at your own risk.
Tangara G10y ago
Hi Nitin,
Tks so much for your reply.
Yes, I could see the column you mentioned. However, after I did what you said and wanted to save the latest and MsSQL gave me the following message :
Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.
You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require
the table to be re-created.
Do I have to drop the table and create again ? or is there something wrong with my Ms SQL?
Midhun Tp10y ago
Hi,
Please check below link -
http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012
Nitin Sontakke10y ago
Can you see the "Column Properties" section in your screen-shot?
Just scroll down a bit in that and you will see "Identity Specifications". Make that Yes and provide Seed and increment values, typically, 1 and 1.
Hope it helps!