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?
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)