CODEWITHSUNDEEP
sql-servert-sqlprimary-keyauto-increment
krishna mohan
krishna mohan

Reputation: 143

how to add primary key column with auto increment in sql server

i have one existing table in that i need to add primary column with auto increment(1,1). how to write query for to insert one primary key column with identity(1,1). getting error -

"Incorrect syntax near the keyword 'IDENTITY".

table ALTER TABLE OLTMS_0B8DF2
        ADD PRIMARY KEY (ID);

i tried like this

ALTER TABLE OLTMS_0B8DF2
ADD PRIMARY KEY (ID) int IDENTITY(1,1);

getting error

Upvotes: 3

Views: 6620

Answers (2)

Gaterde
Gaterde

Reputation: 819

Try this:

ALTER TABLE OLTMS_0B8DF2
   ADD ID INT IDENTITY(1,1)
       CONSTRAINT PK_OLTMS_0B8DF2 PRIMARY KEY CLUSTERED

You first have to create a new column and then you can define this column as the PK.

Upvotes: 7

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You should use inline constraint syntax 

ALTER TABLE OLTMS_0B8DF2
ADD ID INT IDENTITY(1,1) PRIMARY KEY

Upvotes: 2

Related Questions