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?
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.
- 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
Reference: Pinal Dave (https://blog.sqlauthority.com)
