SQL Server Error Messages - Msg 8111

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 / Work Around:

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