Fix Error 8111 “Cannot define PRIMARY KEY constraint on nullable column in table” in SQL Server

If you’re getting SQL Server error 8111 that reads something like “Cannot define PRIMARY KEY constraint on nullable column in table ‘Employees’“, it’s probably because you’re trying to add a PRIMARY KEY constraint to an existing column that is nullable. In other words, the column isn’t defined with a NOT NULL constraint.

We can only add primary keys to columns that are defined as NOT NULL.

To fix this issue, define the table as NOT NULL before attempting to add the primary key.

Example of Error

Suppose we create the following table:

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

And now we try to add a primary key:

ALTER TABLE Employees
    ADD CONSTRAINT PK_Employee_Id PRIMARY KEY (EmployeeID);

This results in the following error message:

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'Employees'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

As mentioned, this happens because the column that we tried to make the primary key is a nullable column.

Solution

To fix this error, define the column as NOT NULL before adding the primary key constraint:

ALTER TABLE Employees
    ALTER COLUMN EmployeeID INT NOT NULL;

Once that has been run, we can now add the primary key constraint:

ALTER TABLE Employees
    ADD CONSTRAINT PK_Employee_Id PRIMARY KEY (EmployeeID);

That code should now run successfully.

We can now check the constraint with the following code:

SELECT *
FROM 
    information_schema.table_constraints
WHERE 
    table_name = 'Employees'
    AND table_schema = 'dbo'
    AND constraint_type = 'PRIMARY KEY';

Or we could use the following code to include more information about the table:

EXEC sp_help 'dbo.Employees';

Existing NULLs

If the table already contains NULL values in that column before attempting to apply the NOT NULL constraint, then it will fail. You’ll need to replace any NULL values with non-NULL values first. Then, once that’s done, you can add the NOT NULL constraint, followed by the primary key constraint.