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

There are moments and times when the 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 any table contain NULL values? Why?

Primary Key and Null in SQL Server - Interview Question of the Week #071 PrimaryKey-800x366

Answer: Primary key on any table in SQL Server can not contain a null value. It is a unique identifier and NULL is not a value that can uniquely identify any row, hence NULL can’t be the 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 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 a 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.

Let me know if you are interested to know more about this topic and I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

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

, ,
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