SQL SERVER – Fix Error: 8111 – Cannot define PRIMARY KEY constraint on nullable column in table – Error: 1750 – Could not create constraint. See previous errors

A very common error new developers receive when they begin with SQL Server and start playing with the keys. Let us first run following code which will generate 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

When you run above code it will give 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.

Now let us see how we can fix the error.

Fix / Workaround / Solution:

The error message is very clear and explains the root cause of the error. When a user creates a primary key on any column, the column should not be nullable.

There are two ways to fix this error.

Method 1: Alter Column as NOT NULL (preferred)

This is the method is very popular as most of the time design of the database is already done so altering the column to not null is an easy thing to do.

-- 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 only useful, if you are aware that Primary Key needs to be NOT NULL. When you design your database or do database modeling at that time, you can keep this in mind and create a table with the column as not null. Additionally, you can also create Primary Key constraint when you create original table.

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

You can read about more about creating a Primary Key over here: SQL SERVER – Create Primary Key with Specific Name when Creating Table.

Reference: Pinal Dave (https://blog.sqlauthority.com)