intro
Learn why primary key columns in SQL must be NOT NULL. Understand how databases enforce non-null constraints and best practices for designing reliable keys.
Question: Can a primary key column in a database table be NULL, or can it ever contain a NULL value? This is a common doubt for those learning about database constraints, since primary keys and unique keys seem similar at first glance.
Let’s find out more about that!
Can a Primary Key Be NULL?
Quick Answer: No, a primary key cannot be NULL.
By definition, a primary key must uniquely identify each row and must have a value. SQL standards and all major databases enforce that primary key columns be NOT NULL. If you attempt to force a NULL into a primary key column, it will violate that constraint with an error like this:
1
ERROR: null value in column "id" of relation "table_name" violates not-null constraint
In fact, when you declare a primary key on a column, the database automatically treats that column as NOT NULL (even if you didn’t explicitly specify NOT NULL in the column definition).
Why a Primary Key Cannot Be Null
A primary key serves as a unique identifier for a record. In other words, every row in the table is identified by its primary key value. If a primary key were NULL, it would mean “unknown identifier” for that row—which defeats the purpose of having a key.
Also, NULL isn’t equal to any other NULL (NULL represents an unknown/missing value), so you couldn’t reliably use it to find or distinguish a row. In practice, database systems ensure this by not allowing NULLs in primary key columns.
When you create a primary key, here’s what happens under the hood:
SQL Primary Keys Must Be Unique and Not Null
According to SQL rules, a UNIQUE column may contain null values, because NULL is not considered equal to another NULL (which is also why the = NULL expression in WHERE clauses doesn’t work as you would expect).
For example, a table could have a unique constraint on email, and you might have a few rows where email is NULL. That doesn’t violate uniqueness because those NULLs aren’t “equal” to each other in the logic of SQL.
However, a primary key is a stronger condition: it implies both uniqueness and not null. In detail, Primary Key = Unique + Not Null.
Think of a primary key as an identifier or an address for a record. If it’s missing (NULL), the record is essentially lost in terms of identification:
Primary Keys Can’t Contain NULLs
The SQL standard explicitly states that primary key columns are not allowed to contain NULLs. This is part of the definition of a primary key.
Here’s an example illustrating the rule:
1
CREATE TABLE students (
2
student_id INT PRIMARY KEY,
3
name VARCHAR(100)
4
);
5
-- student_id is implicitly NOT NULL due to PRIMARY KEY.
6
7
INSERT INTO students(student_id, name) VALUES (NULL, 'Alice');
8
-- This will fail: cannot insert NULL into a PRIMARY KEY column.
If you need a unique identifier but also need to represent “unknown” or “missing” cases, the solution is not to use NULL in a primary key. Instead, you might:
Also, what happens if you try to insert NULL into an auto-increment primary key? In some databases, like MySQL, NULL is treated as the default value, which triggers the database to automatically generate and increment the next value for that column. This doesn't mean that NULL will actually be stored in the primary key. Instead, the database treats the NULL as a signal to assign the next available auto-incremented value.
Conclusion
In summary, a primary key cannot be NULL because it must uniquely identify a record. All databases enforce this either by rule or implicitly converting the column to NOT NULL. If you attempt to declare a primary key on a nullable column, the DBMS will reject it or change it
So, when designing your schema, ensure that the primary key fields always have valid values. If a scenario arises where you think a primary key might not be known, you likely need to rethink your key strategy. Can a primary key be NULL in an SQL Table? No!
