|
|
Error Message
Server: Msg 8147, Level 16, State 1, Line 1
Could not create IDENTITY attribute on nullable column
'<Column Name>', table '<Table Name>'.
Causes
An IDENTITY column provides a unique, incremental value for a column. Identity columns are typically used with PRIMARY KEY constraints (but is not required) to serve as the unique row identifier for the table.
|
When defining an IDENTITY column within a table, the following needs to be considered:
- A table can have only one column defined with the IDENTITY property and the column must have a data type of decimal, int, numeric, smallint, bigint or tinyint.
- The seed and increment can be specified and defaults to 1 for both if not specified.
- The identifier column must not allow for NULL values and must not contain a DEFAULT definition or object.
As can be seen from this list, this error message is generated if the third item in the list above is violated. There are a few ways of encountering this error and this usually occurs with the CREATE TABLE and ALTER TABLE statements.
The following script illustrates how this error is encountered with the CREATE TABLE statement:
CREATE TABLE [dbo].[Company] (
[CompanyID] INT IDENTITY(1, 1) NULL,
[CompanyName] VARCHAR(100)
)
Msg 8147, Level 16, State 1, Line 2
Could not create IDENTITY attribute on nullable column 'CompanyID', table 'dbo.Company'.
The following script illustrates how this error is encountered with the ALTER TABLE statement. The script tries to change the attribute of the IDENTITY column to allow NULL values.
CREATE TABLE [dbo].[Company] (
[CompanyID] INT IDENTITY(1, 1) NOT NULL,
[CompanyName] VARCHAR(100)
)
GO
ALTER TABLE [dbo].[Company] ALTER COLUMN [CompanyID] INT NULL
GO
Msg 8147, Level 16, State 1, Line 2
Could not create IDENTITY attribute on nullable column 'CompanyID', table 'dbo.Company'.
Here’s another way of getting the error with the ALTER TABLE statement. The script tries to add an IDENTITY column into an existing table.
CREATE TABLE [dbo].[Users] (
[UserName] VARCHAR(20) NOT NULL,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
GO
ALTER TABLE [dbo].[Users] ADD [UserID] INT IDENTITY(1, 1) NULL
GO
Msg 8147, Level 16, State 1, Line 2
Could not create IDENTITY attribute on nullable column 'UserID', table 'dbo.Users'.
Solution / Work Around:
To avoid from encountering this error, when defining an identity column make sure that the column will not allow NULL values. In the first scenario earlier, the error can easily be avoided by adding the NOT NULL property when defining the identity column, as can be seen in the following script:
CREATE TABLE [dbo].[Company] (
[CompanyID] INT IDENTITY(1, 1) NOT NULL,
[CompanyName] VARCHAR(100)
)
In the second scenario, since the column is already an IDENTITY column, changing the NULL property of the column to allow NULL values is not permitted. If the column really needs to allow NULL values, then the IDENTITY property of the column needs to be dropped. Removing the IDENTITY property of a column is not a straightforward process. First a new column needs to be added where the original values of the IDENTITY column will be stored. This new column can allow NULL values. Once the new column has been added, its value will be set to the value of the IDENTITY column. Then the IDENTITY column can now be removed from the table and the newly added column can be renamed to the name of the IDENTITY column.
ALTER TABLE [dbo].[Company]
ADD [TempCompanyID] INT NULL
GO
UPDATE [dbo].[Company]
SET [TempCompanyID] = [CompanyID]
GO
ALTER TABLE [dbo].[Company]
DROP COLUMN [CompanyID]
GO
EXECUTE [dbo].[sp_rename] 'Company.TempCompanyID', 'CompanyID', 'COLUMN'
GO
Lastly in the last scenario wherein a new IDENTITY column is being added to an existing table, to avoid this error from happening make sure that the column being added will not allow NULL values, as can be seen in the following ALTER TABLE statement:
ALTER TABLE [dbo].[Users]
ADD [UserID] INT IDENTITY(1, 1) NOT NULL
|