SQL Server Error Messages - Msg 339¶

Error Message¶

Server: Msg 339, Level 15, State 1, Line 1
DEFAULT or NULL are not allowed as explicit identity values.

Causes¶

This error message is a new error message introduced in SQL Server 2005. As the error message suggests, this error occurs when inserting into a table that contains an IDENTITY column and the value being assigned to the IDENTITY column is either DEFAULT or NULL.

To illustrate on how this error happens, given the following table definition:

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

The following INSERT statement will generate the error:

INSERT INTO [dbo].[SuperHero] ( [SuperHeroID], [SuperHeroName], [FirstName], [LastName] )
VALUES ( DEFAULT, 'Superman', 'Clark', 'Kent' )

Msg 339, Level 16, State 1, Line 1
DEFAULT or NULL are not allowed as explicit identity values.

Similarly, the following INSERT statement will also generate the error:

INSERT INTO [dbo].[SuperHero] ( [SuperHeroID], [SuperHeroName], [FirstName], [LastName] )
VALUES ( NULL, 'Spiderman', 'Peter', 'Parker' )

Msg 339, Level 16, State 1, Line 1
DEFAULT or NULL are not allowed as explicit identity values.

Prior to SQL Server 2005, using the same table definition and using the same INSERT statements shown above, a different error message is generated:

INSERT INTO [dbo].[SuperHero] ( [SuperHeroID], [SuperHeroName], [FirstName], [LastName] )
VALUES ( DEFAULT, 'Superman', 'Clark', 'Kent' )

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

INSERT INTO [dbo].[SuperHero] ( [SuperHeroID], [SuperHeroName], [FirstName], [LastName] )
VALUES ( NULL, 'Spiderman', 'Peter', 'Parker' )

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

If the IDENTITY_INSERT property is ON for the table, another error message will be encountered:

SET IDENTITY_INSERT [dbo].[SuperHero] ON
GO

INSERT INTO [dbo].[SuperHero] ( [SuperHeroID], [SuperHeroName], [FirstName], [LastName] )
VALUES ( DEFAULT, 'Superman', 'Clark', 'Kent' )

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'SuperHeroID', table 'dbo.SuperHero'; column does not allow nulls. INSERT fails

SET IDENTITY_INSERT [dbo].[SuperHero] ON
GO

INSERT INTO [dbo].[SuperHero] ( [SuperHeroID], [SuperHeroName], [FirstName], [LastName] )
VALUES ( NULL, 'Superman', 'Clark', 'Kent' )

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'SuperHeroID', table 'dbo.SuperHero'; column does not allow nulls. INSERT fails

Solution / Work Around¶

To avoid this error, when inserting into a table with an IDENTITY column, do not include the IDENTITY column in the INSERT statement and let SQL Server assign the value to this column for each row inserted to the table.

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

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