|
|
Error Message:
Server: Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column
in table 'Table Name'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Causes:
This error is encountered when adding a PRIMARY KEY constraint on a table where
the column being designated as the PRIMARY KEY is defined as NULLABLE.
|
To illustrate, let’s assume that you have the following table definition:
CREATE TABLE [dbo].[Currency] (
[Code] CHAR(3),
[Name] VARCHAR(50)
)
Since the nullability of the columns is not specified, this is determined by the
session settings ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF or by
the database settings ANSI null default. If the ANSI_NULL_DFLT_ON
session setting is ON or the ANSI null default is set to true, then the
nullability of the column will be set to NULL.
Assuming that this is the case, wherein the nullability of both the Code and
Name columns are set to NULL, creating a PRIMARY KEY constraint on the Code
column generates the error:
ALTER TABLE [dbo].[Currency]
ADD CONSTRAINT PK_Currency_Code
PRIMARY KEY ( [Code] )
Server: Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'Currency'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Solution/Workaround:
To avoid this error from happening, first change the nullability of the columns
being designated as the primary key columns to NOT NULL then create the PRIMARY
KEY constraint:
ALTER TABLE [dbo].[Currency]
ALTER COLUMN [Code] CHAR(3) NOT NULL
GO
ALTER TABLE [dbo].[Currency]
ADD CONSTRAINT PK_Currency_Code
PRIMARY KEY ( [Code] )
GO
|