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

Question: Can a primary key contain NULL? No. Every column in a SQL Server primary key is NOT NULL, and the key values must be unique across rows.

A candidate’s answer to this simple question once made me stop and ask a follow-up. Before reading further, try answering it yourself: for a key made of two columns, must each column also be unique on its own?

No. The combination must be unique. An OrderID can repeat across different LineIDs, but the same OrderID and LineID pair cannot appear twice. Both columns must still be NOT NULL.

The key column and another column have different rules

The original identity example allowed FirstName to be NULL while making ID the primary key. This private-session version keeps that distinction and tests an ordinary manually supplied key separately, so an identity-insert rule cannot disguise the NULL-key error:

IF OBJECT_ID('tempdb..#PkIdentity') IS NOT NULL
 OR OBJECT_ID('tempdb..#PkManual') IS NOT NULL
 THROW 50001,'Use a fresh private session.',1;
CREATE TABLE #PkIdentity(ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 FirstName varchar(100) NULL);
INSERT #PkIdentity(FirstName) VALUES(NULL),('Pinal');
SELECT ID,FirstName FROM #PkIdentity ORDER BY ID;

CREATE TABLE #PkManual(ID int NOT NULL PRIMARY KEY NONCLUSTERED);
BEGIN TRY
 INSERT #PkManual VALUES(NULL);
END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER() AS NullKeyError;
END CATCH;
INSERT #PkManual VALUES(1);
BEGIN TRY
 INSERT #PkManual VALUES(1);
END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER() AS DuplicateKeyError;
END CATCH;

The first result has IDs 1 and 2, with NULL permitted in FirstName. The second table rejects a NULL ID with error 515 and a repeated ID with error 2627. Its primary key is explicitly NONCLUSTERED, showing that the no-NULL rule does not depend on a clustered index.

For a permanent table, give the constraint a useful name as demonstrated in creating a primary key with a specific name. Compare this with finding tables without a clustered index.

A UNIQUE constraint and a primary key are related but have different NULL rules. An identity property generates values; it does not by itself declare a primary key. Keeping those three ideas separate is a better interview answer than memorizing the word “unique.”

Related SQL in Sixty Seconds examples: expensive queries, case-sensitive search, wait statistics and multiple backup copies.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.