63

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

samsam114
  • 987
  • 2
  • 8
  • 20
  • 3
    Of course you should not make changes through the GUI. You should write scripts to do tasks and put htem in source control, so that you can easily deploy changes to the database to production. The GUI is a very bad tool to use to change tables especially when they get large. – HLGEM Jul 30 '10 at 15:09

6 Answers6

122
  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".

enter image description here

Sk8erPeter
  • 6,899
  • 9
  • 48
  • 67
Chris Diver
  • 19,362
  • 4
  • 47
  • 58
  • Note: You'll not be able to toggle or change it if you have set the Identity column to another column, make sure you're at the Identity column since there can only be one of it in a table. – Mayer Spitz Feb 11 '19 at 20:35
14

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
andrej
  • 4,518
  • 2
  • 39
  • 39
11

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.

Will A
  • 24,780
  • 5
  • 50
  • 61
8

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

mazenchami
  • 111
  • 1
  • 4
7

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.

Cambium
  • 19,152
  • 3
  • 26
  • 19
0

I think there is a way to do it at definition stage like this

create table employee( id int identity, name varchar(50), primary key(id) ).. I am trying to see if there is a way to alter an existing table and make the column as Identity which does not look possible theoretically (as the existing values might need modification)

user1588737
  • 280
  • 2
  • 6
  • 12