|
|
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
|