Menu
How do I auto increment the primary key in a SQL Server database table? I've had a look through the forum but can't see how to do this.
I've looked at the properties but can't see an option. I saw an answer where you go to the Identity specification property and set it to yes and set the Identity increment to 1, but that section is grayed out and I can't change the no to yes.
There must be a simple way to do this but I can't find it.
626
If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.
To change a primary key to auto_increment, you can use MODIFY command. Let us first create a table. Look at the above sample output, StudentId column has been changed to auto_increment.
Select the column by clicking on the column and then see the Column Properties panel below it. Go to Identity Specifications and explore it. Make (Is Identity) row as Yes and by default Identity Increment row and Identity Seed row become 1.
No. A primary key must be unique and that has to be 100% guaranteed, and NON NULL A primary key should be stable if ever possible and not change.
Make sure that the Key column's datatype is int and then setting identity manually, as image shows
Or just run this code
-- ID is the name of the [to be] identity column ALTER TABLE [yourTable] DROP COLUMN ID ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1) the code will run, if ID is not the only column in the table
image reference fifo's
90
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
