Alter table column for primary key and identity
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.
k-s
Updated on July 05, 2022Comments
-
k-s about 2 years
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?
-
k-s almost 12 yearsYes, thanks for it. I was trying to add identity to my existence column.
-
Martin Smith almost 12 years
-
Beytan Kurt almost 10 yearsif Primary Key is also wanted,
ALTER TABLE LookupStates ADD Id int IDENTITY(1,1) PRIMARY KEY not null
-
chris dorn over 6 yearsIsn't identity the definition of PK in MSSQL?
-
Damien_The_Unbeliever over 6 years@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. -
Build Succeeded over 3 yearsNot directly answering the question.