Cannot Define Primary Key Constraint On Nullable Column In Table

by

Recently, I was working on one critical requirement where I was trying to alter 2 primary keys for a table in SQL server. But after executing the SQL script, I got this error. In this article, I will walk you through the complete steps I followed to fix this issue.

I was executing the below scripts.

First, I executed the below SQL script to create a new table. 

CREATE TABLE TicketBookings (
    TicketNumber VARCHAR(10),
    BookingDate DATE,
    PassengerID INT,
    PSeatNumber VARCHAR(5)
);

Then, I tried executing the script below to create the composite primary key, and after executing this script, I got this error. Check out the screenshot below for your reference.

ALTER TABLE TicketBookings
ADD CONSTRAINT PK_TicketBookings 
PRIMARY KEY (TicketNumber, BookingDate);
Cannot Define Primary Key Constraint On Nullable Column In Table

Table of Contents

Solution

To solve this error, I followed the below steps.

I executed the scripts below to alter the TicketNumber and BookingDate to make it NOT NULL.

ALTER TABLE TicketBookings
ALTER COLUMN TicketNumber VARCHAR(10) NOT NULL;
ALTER TABLE TicketBookings
ALTER COLUMN BookingDate DATE NOT NULL;

The query was executed successfully, as shown in the screenshot below.

cannot define primary key constraint on nullable column in a table
how to add composite primary key to existing table in sql server

Check out Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

Now, I executed the below script again to alter the composite key. This time, the query was executed successfully, and the composite primary keys were also created successfully, as shown in the screenshot below.

ALTER TABLE TicketBookings
ADD CONSTRAINT PK_TicketBookings 
PRIMARY KEY (TicketNumber, BookingDate);
how to add composite primary key to existing table in sql

To cross-check, Just expanded the TicketBookings table node and saw the two primary keys created successfully, as highlighted in the screenshot below.

add composite primary key to existing table in sql

Conclusion

So, to make any column primary key, you need to ensure the column is already set to no null; otherwise, you will get this error. Check out the information mentioned in this article to fix the error that cannot define the primary key constraint on the nullable column in the table.

You may also like following the articles below.