Alter table column for primary key and identity

Question

I have table created and want to alter that table. I want to add a primary key and identity(1,1).

I can apply primary key but applying identity gives error. Is anything missing?

ALTER TABLE MyTable ADD PRIMARY KEY (Id)

How can I add identity as well with primary key?

Answer 1

You cannot alter the definition of an existing column in the database, to add the IDENTITY property (nor to remove it). You have to create a new column with the IDENTITY property:

ALTER TABLE MyTable ADD NewID int IDENTITY(1,1) not null

Unfortunately, you're not then able to assign the old ID values to this new column. If you want to assign the ID values, and then let IDENTITY take over, you'd be better off creating a new table with the structure you want, then importing data from the old table (you can use IDENTITY_INSERT to assign values to the IDENTITY column).

You would then drop the old table and rename the new table, if required.