Primary Key and Null in SQL Server – Interview Question of the Week #071

There are moments and time when interviewer sometimes gets highly disappointed. Well, I had such a moment a few days ago, when I asked a very simple question about Primary Key (PK) to a candidate. Before you look at the answer – please try to answer this question yourself first. If your answer is incorrect. Assume that you could have disappointed me if you were there in the interview.

Question: Can Primary Key (PK) on the any table contain NULL values? Why?

Primary Key and Null in SQL Server - Interview Question of the Week #071 key

Solarwinds

Answer: Primary key on any table in SQL Server can not contain null value. It is a unique identifier and NULL is not a value which can uniquely identify any row, hence NULL can’t be Primary Key value for any table in SQL Server. The Allow Nulls property can’t be set on a column that is part of the PK. All columns that are part of a table’s a PK must contain aggregate unique values other than NULL.

I guess, that’s it. It is that simple.

Here is a blog post about Primary Key I wrote earlier where I demonstrate how one can create a PK with specific name.

CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](100) NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
([ID] ASC)
)
GO

One more blog post which I had written about how the same subject earlier where I talk about a clustered index and its relationship with PK.

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

Solarwinds
, ,
Previous Post
List All the Stored Procedure Modified in Last Few Days – Interview Question of the Week #070
Next Post
SQL Domain Controller – Interview Question of the Week #072

Related Posts

Leave a Reply

Menu