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. The question was about primary key and NULL, and it is simpler than it looks.
Question: Can Primary Key (PK) on any table contain NULL values? Why?
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)
)
GOOne 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.
- Find Expensive Queries – SQL in Sixty Seconds #159
- Case-Sensitive Search – SQL in Sixty Seconds #158
- Wait Stats for Performance – SQL in Sixty Seconds #157
- Multiple Backup Copies Stripped – SQL in Sixty Seconds #156
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
