CODEWITHSUNDEEP
sql-server-2008ssms
samsam114
samsam114

Reputation: 977

Auto-increment primary key in SQL tables

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

Upvotes: 64

Views: 194115

Answers (6)

andrej
andrej

Reputation: 4743

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

Upvotes: 14

user1588737
user1588737

Reputation: 320

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)

Upvotes: 0

mazenchami
mazenchami

Reputation: 131

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

Upvotes: 8

Chris Diver
Chris Diver

Reputation: 19802

  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

Upvotes: 125

Cambium
Cambium

Reputation: 19392

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.

Upvotes: 7

Will A
Will A

Reputation: 24988

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.

Upvotes: 11

Related Questions