13

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
k-s
  • 2,192
  • 11
  • 39
  • 73

3 Answers3

28

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Yes, thanks for it. I was trying to add identity to my existence column. – k-s Aug 10 '12 at 08:19
  • 1
    [You can do this as a metadata only change](http://stackoverflow.com/q/6084572/73226) – Martin Smith Aug 10 '12 at 10:16
  • 2
    if **Primary Key** is also wanted, `ALTER TABLE LookupStates ADD Id int IDENTITY(1,1) PRIMARY KEY not null` – Beytan Kurt Aug 12 '14 at 11:46
  • Isn't identity the definition of PK in MSSQL? – chris dorn Oct 13 '17 at 15:17
  • @chrisdorn - no, don't confuse separate concepts. A primary key is `one or more columns by which each row may be uniquely identified`. `identity` is a means to apply a unique value per row in a single column. One may be used to create the other but they are *not* the same thing. Generally referred to as a surrogate, and if you don't *also* have a natural key then you're not modelling *data* but random assortments of facts. – Damien_The_Unbeliever Oct 13 '17 at 16:54
0

I found this because I was looking for the same answer to assign IDENTITY programmatically to a table in SQL Server 2019. My situation is that the table I was trying to apply this to was an imported table where the column that I was attempting to apply IDENTITY to had IDENTITY in the table where it was imported from so it met the requirements of IDENTITY - each value was unique and had the correct data type. This and other research helped me to understand I could not do this programmatically.

But it does not prevent me from actually accomplishing the result. Instead of programmatically, I can open the table in design view in SSMS and assign IDENTITY there so long as seed and identity interval match the original setup of the table. Obviously, not as quick a solution if a lot of tables are involved but protects the integrity of IDENTITY values if they are used as Foreign Keys in other associated tables.

StuKH
  • 31
  • 7
0

It is possible, however, it has to do done in the design mode and you will have to specify the column in question as identity. Right click on the table, pick "Design", go to the column, in the "Column Properties" look for "Identity Specification" and you are all set.

Stefan Gabor
  • 340
  • 3
  • 7