Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Query String Parser Function
Error Messages
Home > SQL Server Error Messages > Msg 8102 - Cannot update identity column 'Column Name'.
SQL Server Error Messages - Msg 8102 - Cannot update identity column 'Column Name'.

Error Message

Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'Column Name'.

Causes

As the message suggests, this error happens when you are trying to update the value of an identity column.  To illustrate, let’s say you have a table containing the names of super heroes.

CREATE TABLE [dbo].[SuperHeroes] (
    [SuperHeroID]   INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [SuperHeroName] VARCHAR(20),
    [FirstName]     VARCHAR(50),
    [LastName]      VARCHAR(50)
)

INSERT INTO [dbo].[SuperHeroes] ( [SuperHeroName], [FirstName], [LastName] )
VALUES ( 'superman', 'Clark', 'Kent' )

INSERT INTO [dbo].[SuperHeroes] ( [SuperHeroName], [FirstName], [LastName] )
VALUES ( 'spiderman', 'Peter', 'Parker' )

INSERT INTO [dbo].[SuperHeroes] ( [SuperHeroName], [FirstName], [LastName] )
VALUES ( 'batman', 'Bruce', 'Wayne' )

SELECT * FROM [dbo].[SuperHeroes]

SuperHeroID SuperHeroName     FirstName   LastName
----------- ----------------- ----------- ---------
1           superman          Clark       Kent
2           spiderman         Peter       Parker
3           batman            Bruce       Wayne

Along the way, you want to change the numbering of the IDs and re-assign the IDs of the superheroes moving them up by 100.  To do this, you issue the following UPDATE command:

UPDATE [dbo].[SuperHeroes]
SET [SuperHeroID] = [SuperHeroID] + 100
WHERE [SuperHeroID] < 100

But since the SuperHeroID is an identity column, you get the following error message:

Msg 8102, Level 16, State 1, Line 2
Cannot update identity column 'SuperHeroID'.

Setting the IDENTITY_INSERT property of the table to ON will not make any difference as this only affects new records and not existing ones.

Solution / Workaround:

If you really need to change the values of an identity column, this can be accomplished in 2 steps.  The first step is to INSERT new records containing the same information of the existing records whose identity column needs to be updated assigning the new IDs for these new records.  The second step is deleting the original records.

Since you will be assigning the values for the identity column, issuing the following INSERT statement will generate an error:

INSERT INTO [dbo].[SuperHeroes] ( [SuperHeroID], [SuperHeroName], [FirstName], [LastName] )
SELECT [SuperHeroID] + 100, [SuperHeroName], [FirstName], [LastName]
FROM [dbo].[SuperHeroes]
WHERE [SuperHeroID] < 100

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'SuperHeroes' 
when IDENTITY_INSERT is set to OFF.

To overcome this, as the error message suggests, you have to set the IDENTITY_INSERT property of the table to ON then issue the INSERT statement.  Make sure to reset the IDENTITY_INSERT property back to OFF.

SET IDENTITY_INSERT [dbo].[SuperHeroes] ON
GO

INSERT INTO [dbo].[SuperHeroes] ( [SuperHeroID], [SuperHeroName], [FirstName], [LastName] )
SELECT [SuperHeroID] + 100, [SuperHeroName], [FirstName], [LastName]
FROM [dbo].[SuperHeroes]
WHERE [SuperHeroID] < 100
GO

SET IDENTITY_INSERT [dbo].[SuperHeroes] OFF
GO

SELECT * FROM [dbo].[SuperHeroes]


UserID UserName  FirstName LastName
------ --------- -------- --------
1      superman  Clark    Kent
2      spiderman Peter    Parker
3      batman    Bruce    Wayne
101    superman  Clark    Kent
102    spiderman Peter    Parker
103    batman    Bruce    Wayne

After creating duplicate records whose identity column you want to change, the next step is to delete the original records.

DELETE FROM [dbo].[SuperHeroes]
WHERE [SuperHeroID] < 100
GO

Deleting the original records will be no problem if the table you are deleting from is not being referenced by another table in a foreign key relationship.  If the table you are deleting from is being referenced by another table in a foreign key relationship, deleting the original records will generate an error.

To illustrate, let’s assume you have another table that contains the different villains each super hero faces.

CREATE TABLE [dbo].[Villains] (
    [VillainID]     INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [VillainName]   VARCHAR(50),
    [SuperHeroID]   INT NOT NULL REFERENCES [dbo].[SuperHeroes] ( [SuperHeroID] )
)
GO

INSERT INTO [dbo].[Villains] ( [SuperHeroID], [VillainName] )
VALUES ( 1, 'Lex Luthor' )

INSERT INTO [dbo].[Villains] ( [SuperHeroID], [VillainName] )
VALUES ( 2, 'Green Goblin' )

INSERT INTO [dbo].[Villains] ( [SuperHeroID], [VillainName] )
VALUES ( 2, 'Doctor Octopus' )

INSERT INTO [dbo].[Villains] ( [SuperHeroID], [VillainName] )
VALUES ( 3, 'Joker' )

INSERT INTO [dbo].[Villains] ( [SuperHeroID], [VillainName] )
VALUES ( 3, 'Riddler' )

SELECT * FROM [dbo].[Villains]

VillainID  VillainName     SuperHeroID
---------  --------------- -------
1          Lex Luthor      1
2          Green Goblin    2
3          Doctor Octopus  2
4          Joker           3
5          Riddler         3

Issuing the DELETE statement earlier will generate an error:

DELETE FROM [dbo].[SuperHeroes]
WHERE [SuperHeroID] < 100
GO

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint 
"FK_Villains_SuperHeroes".  The conflict occurred in database "SQL2008", table "dbo.Villains",
column 'SuperHeroID'.
The statement has been terminated.

To overcome this error, what needs to be done is update the referenced records in the second table and point it to the newly created records in the first table.

UPDATE [dbo].[Villains]
SET [SuperHeroID] = [SuperHeroID] + 100
GO

SELECT * FROM [dbo].[Villains]

VillainID VillainName    SuperHeroID
--------- -------------- -----------
1         Lex Luthor     101
2         Green Goblin   102
3         Doctor Octopus 102
4         Joker          103
5         Riddler        103

Then the original records from the [dbo].[SuperHeroes] table can now be deleted since these records with the original IDs are not being reference anymore.

DELETE FROM [dbo].[SuperHeroes]
WHERE [SuperHeroID] < 100
GO

SELECT * FROM [dbo].[SuperHeroes]

UserID UserName  FirstName LastName
------ --------- --------- --------
101    superman  Clark     Kent
102    spiderman Peter     Parker
103    batman    Bruce     Wayne
Related Articles :