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

Question

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.

Answer 1

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 - <br> https://support.microsoft.com/en-in/kb/956176

Answer 2

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.

Answer 3

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?

Answer 4

Hi,

Please check below link -

http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012

Answer 5

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!