Question: Can a primary key column contain NULL?
Answer: No. SQL Server requires every column in a primary key to be NOT NULL, as well as enforcing uniqueness of the complete key.
I was helping a large organization interview performance-tuning candidates. We spoke to about fifty candidates, offered two positions, and one had accepted when I wrote the post. The panel kept returning to this question, and most candidates answered it correctly. I still wonder why it is such an interview favorite.
The rule is explicit in SQL Server’s primary key definition. It is not simply because “two NULLs are unequal.” Comparisons involving NULL ordinarily return UNKNOWN, and SQL Server’s UNIQUE constraints have their own NULL behavior. A single nullable unique column can permit one NULL; that does not turn it into a nullable primary key.
IF OBJECT_ID(N'dbo.SqlaNullablePK38003', N'U') IS NOT NULL
OR OBJECT_ID(N'dbo.SqlaPresentPK38003', N'U') IS NOT NULL
THROW 50001, 'An experiment table already exists.', 1;
BEGIN TRY
EXEC(N'CREATE TABLE dbo.SqlaNullablePK38003
(ID int NULL, Col1 nvarchar(60) NOT NULL,
CONSTRAINT PK_SqlaNullable38003 PRIMARY KEY CLUSTERED (ID));');
END TRY
BEGIN CATCH
SELECT N'Nullable primary key definition rejected' AS Test,
ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
CREATE TABLE dbo.SqlaPresentPK38003
(ID int NOT NULL PRIMARY KEY, Col1 nvarchar(60) NOT NULL);
BEGIN TRY
INSERT dbo.SqlaPresentPK38003 VALUES (NULL, N'Missing identifier');
END TRY
BEGIN CATCH
SELECT N'NULL insert rejected' AS Test,
ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
DROP TABLE dbo.SqlaPresentPK38003;The first test keeps the original declaration with ID explicitly NULL. Its Messages include error 8111 and the follow-on 1750; TRY/CATCH can report the final error in that sequence. The second definition is valid, but inserting NULL into its ID column fails with error 515. The disposable valid table is then removed.
For a composite primary key, every participating column must be NOT NULL, while the combination must be unique. If you omit nullability when defining a primary key, SQL Server makes the key columns NOT NULL; explicitly declaring NULL is what makes the first test fail.
If a business identifier can be missing, model that separately from the primary key instead of using a nullable primary identifier. The key may be clustered or nonclustered; that choice does not change its NULL rule. Send me other interview questions you would like the series to explain.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
8 Comments. Leave new
What about unique key. How unique key maintain only one null value.
What about unique key. How it maintains only one null values.
Pinal, “If two records of a single column have a NULL value, the column values are not considered equal”, then why GROUP BY considers NULLS as same when more than 1 record have nulls and groups by as same. Same case with distinct.
Purpose of Primary Key cannot have Nulls : It should contain a valid atomic value which uniquely identifies record in the table, which can be indexed and further be linked to other tables using Foreign Keys. Nulls are UNKNOWN and unknowns cannot be referred nor is a candidate value and so Primary Key cannot have nulls.
some examples :
create table test_nulls (id int identity(1,1),
col_value varchar(25))
go
insert into test_nulls(col_value)
values(null), (‘NULL’), (‘some value’), (null),(’10’), (null),(‘text’)
go
select * from test_nulls
select col_value, count(1) from test_nulls
group by col_value
Purpose of Primary Key cannot have Nulls : It should contain a valid atomic value which uniquely identifies record in the table, “which can be indexed and further be linked to other tables using Foreign Key. Nulls are UNKNOWN and unknowns cannot be referred …”
Not agreed with this statement.
To reference any field as foreign key, its not necessary that it should be primary key. Unique key can also referred as foreign key. Unique key can contain NULL values and so Foreign key can contain NULL values too.
Yes its true that Unique Key can also be referred as foreign keys, Thanks for letting me know this !! But you cannot make Unique keys as potential candidate unless imposing NOT NULL. And its of no value to represent NULL as a candidate value and so Primary Keys cannot have NULLs. On the other hand, attributes with Primary Keys when referred as foreign keys in child tables can have NULLs for which parent value is unknown.
Hi Pinal.As far as I Know.Primary key is a combination of NOt NULL and Unique .Then according to that concept it will not accept null values.
In concern with UNIQUE constraint it will only accept 1 NULL value
A primary key is not only a unique identifier but also a value that can satisfy a predicate such as WHERE = , used to look up a unique row (assuming a simple PK). A NULL cannot directly substitute for a non-NULL value in that role, even if it is unique, because either a different predicate would be required depending on whether or not the key value was NULL or else a non-SARGable expression would have to be used in place of the PK column name in the predicate. NULL is just not on an equal footing with non-NULL values when used to uniquely identify rows.
So maybe it is a useful interview question after all?
I’ve never been able to understand why a SQL NULL doesn’t behave logically, like null in other programming languages. NULL is the absence of a value. There is nothing ‘unknown’ about it. And, as others have stated, SQL is terribly inconsistent with it, with a UNIQUE constraint only allowing a single NULL value, despite it being unequal to NULL, and therefore, according to the rules, never qualifying for uniqueness.