Auto increment primary key in SQL Server Management Studio 2012
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.
I've looked the the properties but can't see an option, I have seen 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.
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
When you're creating the table, you can create an IDENTITY
column as follows:
CREATE TABLE (
ID_column INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
...
);
The IDENTITY
property will auto-increment the column up from number 1. (Note that the data type of the column has to be an integer.) If you want to add this to an existing column, use an ALTER TABLE
command.
Edit:
Tested a bit, and I can't find a way to change the Identity properties via the Column Properties window for various tables. I guess if you want to make a column an identity column, you HAVE to use an ALTER TABLE
command.
You have to expand the Identity section to expose increment and seed.
Edit: I assumed that you'd have an integer datatype, not char(10). Which is reasonable I'd say and valid when I posted this answer
Expand your database, expand your table right click on your table and select design from dropdown.
Now go Column properties below of it scroll down and find Identity Specification, expand it and you will find Is Identity make it Yes. Now choose Identity Increment right below of it give the value you want to increment in it.
Perhaps I'm missing something but why doesn't this work with the SEQUENCE object? Is this not what you're looking for?
Example:
CREATE SCHEMA blah.
GO
CREATE SEQUENCE blah.blahsequence
START WITH 1
INCREMENT BY 1
NO CYCLE;
CREATE TABLE blah.de_blah_blah
(numbers bigint PRIMARY KEY NOT NULL
......etc
When referencing the squence in say an INSERT command just use:
NEXT VALUE FOR blah.blahsequence
More information and options for SEQUENCE