A Primary Key doesn’t allow NULL values because it must uniquely identify each row in the table. If NULLs were allowed, the database wouldn’t know which row is which.
But here’s an interesting twist — in text columns, it allows one blank value (”) because that’s not considered NULL, just an empty but valid entry
Let’s see a practical example that demonstrates how Primary Key disallow NULL(s).
Let’s take an example of a Employee table where EmpId is the Primary Key
CREATE TABLE dbo.Employee
(Empid INT PRIMARY KEY,
Name NVARCHAR(100),
Age INT,
Joining_Date DATE
)
GO
Now let’s try to insert NULL value in the EmpId Column which is a Primary Key
INSERT INTO dbo.Employee (Empid, Name, Age,Joining_Date)
VALUES (12345, 'John Adam', 25,'2024-06-27');
GO
Insertion failed with the below error:
Msg 515, Level 16, State 2, Line 18
Cannot insert the value NULL into column ‘Empid’, table ‘BI_Reporting.dbo.Employee’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Let’s take case of BLANK VALUE
We are able to insert one blank value but if we again want to insert blank value it will give the below error.
Msg 2627, Level 14, State 1, Line 18
Violation of PRIMARY KEY constraint ‘PK__Employee__AF2EBFA1316464D9’. Cannot insert duplicate key in object ‘dbo.Employee’.
The duplicate key value is (0).
Hence, a Primary Key ensures data integrity by rejecting NULLs and duplicates—allowing only values that uniquely identify each record. Interestingly, in text columns, it does allow one blank (”), as it’s treated as a valid, non-null value