When starting out with SQL Server, new developers often encounter a common error related to keys. Let’s take a look at an example code snippet that generates an error (8111):

-- Create Table
CREATE TABLE test (
    ID INT,
    Col1 INT,
    Col2 VARCHAR(100)
)
GO

-- Now create PK on ID Col
ALTER TABLE test ADD CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ([ID] ASC)
GO

Running the above code will result in the following error:

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

The error message clearly explains the root cause of the issue. When creating a primary key on a column, the column should not be nullable. There are two ways to fix this error:

Method 1: Alter Column as NOT NULL (preferred)

This method is commonly used when the database design is already in place. Simply alter the column to be not null:

-- Alter Column to not null
ALTER TABLE test ALTER COLUMN ID INT NOT NULL
GO

Method 2: Create a table with the Column as NOT NULL

This method can be useful if you are aware that the primary key needs to be not null. During the database design or modeling phase, you can create a table with the column as not null and also add the primary key constraint:

-- Create Table
CREATE TABLE test (
    ID INT NOT NULL,
    Col1 INT,
    Col2 VARCHAR(100),
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ([ID] ASC)
)
GO

By following either of these methods, you can avoid the error and successfully create a primary key constraint on your SQL Server table.

For more information on creating a primary key, you can refer to the SQL SERVER – Create Primary Key with Specific Name when Creating Table article.