Can We Have NULL Value in Primary Key? – Interview Question of the Week #075

This is a very interesting question. I really do not know why interviews are fascinated with this question. One of the professional services I offer is to help organizations select an ideal employee for their organization and last week, I was on a conference call with a large MNC who was looking for performance tuning expert for their organization. We interviewed around 50 different employees and offered jobs to 2 experts and 1 has so far accepted it. One of the questions which I see my friends from the interview panel asking again and again was about Primary Key. I noticed that most of the candidate got it correct.

Question: Can we have NULL Value in the Primary Key Column?

Answer: No. We can’t have a Primary Key column with a NULL value.

The reason for the same is very simple, primary key purpose is to uniquely identify records. If two records of a single column have a NULL value, the column values are not considered equal. In simple words two NULL values are not considered as equal. This is the reason, Primary Key can’t have NULL values as they are not compared with any other value.

Here is a simple script if you execute, it will throw an error because Primary Key can’t have NULL value.

-- Following script will give ERROR
CREATE TABLE TestTable(
	[ID] [int] NULL,
	[Col1] [nvarchar](60) NOT NULL	
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
))
GO

Here is the output, which we receive when we execute above query.

Well, that’s it for today. Do let me know if you have any other interview question, which other users might be interested to read.

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