Why Primary key can't be null

Technically, a primary key constraint is simply a combination of a
unique constraint and a not-null constraint.


A primary key indicates that a column or group of columns can be used as
a unique identifier for rows in the table. (This is a direct consequence
of the definition of a primary key. Note that a unique constraint does
not, by itself, provide a unique identifier because it does not exclude
null values.) This is useful both for documentation purposes and for
client applications.

It comes from SQL specification:

Quote:

A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns. In
addition, if the unique constraint was defined with PRIMARY KEY,
then it requires that none of the values in the specified column or
columns be the null value.

For the full SQL92 - http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt[^]

Let me explain this practically
consider the following Situation

CREATE TABLE CnLanguage
(
	Languageid INT PRIMARY KEY, LanguageNAme VARCHAR(100)
)
GO
CREATE TABLE CnState
(
	Stateid INT PRIMARY KEY, StateName VARCHAR(100)
)
GO
CREATE TABLE Person
(
	id INT PRIMARY KEY, PNAME VARCHAR(100), Languageid INT FOREIGN KEY REFERENCES Cnlanguage(Languageid), 
	Stateid INT FOREIGN KEY REFERENCES Cnstate(Stateid)
)

INSERT INTO dbo.CnLanguage (Languageid, LanguageNAme) VALUES (1, 'English')
INSERT INTO dbo.CnState (Stateid, StateName) VALUES (1, 'Test State')

now if we want to insert a person name without provide the data for state and language,
it means he is not relate to any state or any language(just an assumption)
then how will you insert a record in that table person?
simple

INSERT INTO dbo.Person(id, PNAME, Languageid, Stateid)VALUES(1, 'Testperson', NULL, NULL)

it means that the person is not relate to any state or any lanugage

Now consider if we allow nulls in primary key
then how can you insert such a record in person table
you will not able to insert a record since there is no primary key found in the table
still if you try to insert it like this
(just an assumption )

INSERT INTO dbo.CnLanguage (Languageid, LanguageNAme) VALUES (NULL, 'Nothing')
INSERT INTO dbo.CnState (Stateid, StateName) VALUES (Null, 'Nothing')
INSERT INTO dbo.Person(id, PNAME, Languageid, Stateid)VALUES(1, 'Testperson', NULL, NULL)

then it means the person is relate to some language and some state
it is completely contradiction to our situation isn't it?
it's clear now why we are not use the null in primary keys isn't it?

Primary is a combination of UNIQUE key and NOT NULL key .A primary key constraint does not imply theNOT NULLconstraint in practice. Because NULL is not an actual value (it represents the lack of a value), when two rows are compared, and both rows have NULL in a column, the column values are not considered to be equal. Thus, in order for a unique key to uniquely identify each row in a table, NULL values must not be used