2

I have created a table and forgot to set auto-increment to "id" column.

As that column is "primary key" now I'm not able to alter column (change the identity).

What could I do to add the auto-increment option?

I'm using Microsoft SQL Server.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
Vicky S
  • 21
  • 3
  • Take off Primary Key constraint. Modify column to auto increment. Add Primary Key constraint back – Stivan Aug 29 '16 at 19:10
  • Before Kamil's edit, your question said that you were using `Microsoft SQL`. I assume that means `SQL Server`, so I added the relevant tag. If that's not the case, please fix the text and tag. – sstan Aug 29 '16 at 19:28
  • Hi, Welcome to stackoverflow. Please search before you ask a question. – Ashkan S Aug 29 '16 at 19:45
  • @Stivan: even if the columns is not the primary key, there's no way in SQL Server to *add* the `Identity` specification to an existing column. You must drop the column and re-create it *with* the `identity` specificatoin – marc_s Aug 29 '16 at 20:30

2 Answers2

2

Follow the below steps.

  1. Remove the primary key from the column.

  2. Add a new column with auto increment identity value

  3. Update the related tables with new column values where ever the old column is referred as foreign key.

  4. Drop the old column

  5. Rename the new column with dropped column name.

  6. Set it as primary key.

  7. Make sure all other constraints are in place

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
0

First you need to drop the "id" column.

ALTER TABLE <Your_Table>
DROP COLUMN id

Then you need to recreate the "id" column, but now with the IDENTITY constraint.

ALTER TABLE <Your_Table>
ADD id INT NOT NULL IDENTITY PRIMARY KEY
KuboK
  • 68
  • 1
  • 8