Auto-increment primary key in SQL tables

Question

Using Sql Express Management Studio 2008 GUI (not with coding), how can I make a primary key auto-incremented?

Let me explain: there is a table which has a column named "id" and the items of this column are set to be primary keys. I want to make this column auto-incremented, but how?

Cheers

Answer 1

1. Presumably you are in the design of the table. If not: right click the table name - "Design".
2. Click the required column.
3. In "Column properties" (at the bottom), scroll to the "Identity Specification" section, expand it, then toggle "(Is Identity)" to "Yes".

Answer 2

Although the following is not way to do it in GUI but you can get autoincrementing simply using the IDENTITY datatype(start, increment):

CREATE TABLE "dbo"."TableName"
(
   id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
   name varchar(20),
);

the insert statement should list all columns except the id column (it will be filled with autoincremented value):

INSERT INTO "dbo"."TableName" (name) VALUES ('alpha');
INSERT INTO "dbo"."TableName" (name) VALUES ('beta');

and the result of

SELECT id, name FROM "dbo"."TableName";

will be

id    name
--------------------------
1     alpha
2     beta

Answer 3

Right-click on the table in SSMS, 'Design' it, and click on the id column. In the properties, set the identity to be seeded @ e.g. 1 and to have increment of 1 - save and you're done.

Answer 4

for those who are having the issue of it still not letting you save once it is changed according to answer below, do the following:

tools -> options -> designers -> Table and Database Designers -> uncheck "prevent saving changes that require table re-creation" box -> OK

and try to save as it should work now

Answer 5

I don't have Express Management Studio on this machine, so I'm going based on memory. I think you need to set the column as "IDENTITY", and there should be a [+] under properties where you can expand, and set auto-increment to true.