A primary key column cannot contain NULL values because the purpose of a primary key is to uniquely identify every row in a table. A NULL value represents 'unknown' or 'missing' data, which cannot be used for unique identification.

1. Reasons PRIMARY KEY Cannot Be NULL

1
A primary key must uniquely identify each row — NULL cannot uniquely identify anything.
2
NULL is not a value; it means 'unknown', which breaks uniqueness rules.
3
MySQL automatically enforces PRIMARY KEY columns as NOT NULL.
4
Allowing NULLs would prevent the database from ensuring row uniqueness and referential integrity.

2. What MySQL Does Internally

1
MySQL automatically adds an implicit NOT NULL constraint to PRIMARY KEY columns.
2
If you try to insert NULL into a primary key column, MySQL will throw an error.
3
Composite primary keys also require that none of the columns involved contain NULL.

Example: Attempting to Insert NULL into a Primary Key

In summary, primary keys must be NOT NULL and unique so that each row is reliably identifiable. Allowing NULLs would violate this fundamental rule.

Difficulty: 2/10

Topics: primary key constraints, NULL handling, MySQL schema design

Scenario Questions

0-2 years experience

1
You need to create a users table where email should be the primary key, but some existing rows have NULL emails. How would you handle the schema creation?
2
If you run INSERT INTO t (id) VALUES (NULL); after defining id INT PRIMARY KEY, what happens and why?
3
A teammate defined a primary key on a column that currently allows NULLs. What would you explain about MySQL's behavior?

2-5 years experience

1
During a bulk data import, rows with NULL in the intended primary key are being rejected. Walk me through how you would diagnose and resolve the problem.
2
Your feature requires a composite primary key on order_id and item_seq, but item_seq can be missing for some records. What trade‑offs do you consider and how would you enforce uniqueness?
3
A bug shows duplicate rows after a bulk insert, and you suspect the primary key definition allowed NULLs. How would you investigate and fix it?

5-8 years experience

1
We have a high‑traffic orders table with an auto‑increment primary key, but business wants to allow orders to be created without an ID initially. How would you redesign the schema while keeping uniqueness and performance?
2
In a sharded MySQL deployment, some shards mistakenly defined the primary key column as nullable. What are the implications for routing, consistency, and how would you remediate?
3
When migrating a legacy system where primary keys were defined as nullable INTs, describe the steps to clean the data and enforce NOT NULL without causing downtime.

8+ years experience

1
Our company is consolidating several legacy databases into a unified data lake, and many source tables have primary key columns that were historically nullable. At an architectural level, how would you normalize these schemas, handle existing NULLs, and protect downstream services?
2
A cross‑team initiative wants to replace all primary key constraints with surrogate UUID keys to avoid NULL issues and improve global uniqueness. What are the long‑term trade‑offs, migration strategy, and impact on indexing and replication?
3
If you were to build a framework for schema evolution that automatically prevents nullable primary keys across all services, what policies, tooling, and governance would you put in place?

Follow-up Questions

❓What error does MySQL return if you try to insert NULL into a primary key column?
❓How does MySQL treat an empty string versus NULL in a VARCHAR primary key?
❓If you need an optional identifier that must still be unique, what alternative constraint would you use?