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

• SQL Server Error Messages - Msg 273 - Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
• SQL Server Error Messages - Msg 515 - Cannot insert the value NULL into column 'Column Name', table 'Table Name'; column does not allow nulls. INSERT fails.
• SQL Server Error Messages - Msg 544 - Cannot insert explicit value for identity column in table <Table Name> when IDENTITY_INSERT is set to OFF.
• SQL Server Error Messages - Msg 1781 - Column already has a DEFAULT bound to it.