What Is a Primary Key, and What Happens Without One?

A primary key is a promise. It says that every row in this table can be told apart from every other row, for as long as the table exists. That sounds obvious until you meet a table without one, and discover that nobody can safely update a single row in it.

The Cloakroom

Think of a cloakroom with two hundred coats on a rail. Half of them are black. Without a ticket, finding your coat means checking pockets. With a ticket, it takes ten seconds.

The ticket is the primary key. It is not the coat, and it does not describe the coat. Its only job is to point at exactly one coat, and never at two.

Two Rules, Enforced by the Database

A primary key enforces exactly two things, and it does it for you rather than trusting your application to remember.

The first rule is that values cannot repeat. The second is that values cannot be NULL. Here is the table, and both refusals, run on SQL Server 2025:

CREATE TABLE dbo.Customer (
    CustomerID int NOT NULL PRIMARY KEY,
    Name nvarchar(50)
);

INSERT dbo.Customer VALUES (NULL, 'Nobody');

Cannot insert the value NULL into column 'CustomerID',
table 'BasicsLab.dbo.Customer'; column does not allow nulls. INSERT fails.

INSERT dbo.Customer VALUES (1, 'Amy');
INSERT dbo.Customer VALUES (1, 'Amy again');

Violation of PRIMARY KEY constraint 'PK__Customer__A4AE64B8...'.
Cannot insert duplicate key...

NULL is refused for a good reason. NULL means unknown, and an unknown cannot identify anything. A ticket with nothing written on it is not a ticket.

What Happens Without One

A table with no primary key will happily hold rows you cannot tell apart. Watch what that allows:

CREATE TABLE dbo.NoKey (id int NULL, Name nvarchar(50));
INSERT dbo.NoKey VALUES (1, 'Amy'), (1, 'Amy'), (NULL, 'Ghost');

SELECT COUNT(*) AS rows_in_nokey, COUNT(DISTINCT id) AS distinct_ids
FROM dbo.NoKey;

rows_in_nokey  distinct_ids
3              1

Three rows, one distinct identifier. Two of them are byte for byte identical. Now try to correct a typo in one of the Amy rows. Any UPDATE you write touches both. Any DELETE removes both. There is no statement you can write that reaches one and not the other.

That is the real cost. Not performance, not tidiness. You lose the ability to change a single row.

The Structure Underneath

A primary key in SQL Server creates an index by default, and that index is clustered unless you say otherwise. Clustered means the table’s own rows are stored in that order. Here are those two tables side by side:

SELECT t.name AS table_name, i.type_desc AS structure
FROM sys.tables AS t
JOIN sys.indexes AS i ON i.object_id = t.object_id AND i.index_id IN (0, 1)
WHERE t.name IN ('Customer', 'NoKey');

table_name  structure
Customer    CLUSTERED
NoKey       HEAP

A heap is a table with no order at all, just pages of rows in whatever sequence they arrived. Heaps are not automatically wrong, and for a staging table you load and truncate they are often the right choice. As a permanent home for business data they are usually an accident rather than a decision.

Natural or Made Up

A natural key uses data that already means something, such as an email address or a product code. A surrogate key is a number the database invents, usually an IDENTITY column.

I reach for a surrogate key almost every time. Natural keys have a habit of changing. Somebody changes their email address, a product gets renumbered after a merger, and now a value that was supposed to be permanent has to be updated everywhere it was copied to.

One warning on surrogate keys. A random GUID as the clustered primary key spreads your inserts across the whole table instead of adding them at the end, and that costs you in page splits and fragmentation. If you want a GUID, look at whether it needs to be the clustered key or just a unique column.

Unique Constraint Is Not the Same

A unique constraint also refuses duplicates, so people treat the two as interchangeable. They are not.

A table has one primary key and can have many unique constraints. A unique constraint allows a single NULL, because a second NULL would have to be proved equal to the first and cannot be. A primary key allows none.

A sensible pattern is both. A surrogate primary key for the database to work with, and a unique constraint on the natural value, such as email, so the real duplicate never gets in.

When I look at a database and find tables with no primary key, it is rarely a design choice. It is almost always a table that started as a quick import and became permanent while nobody was looking.

A primary key is not a column, it is the promise that one row can always be told from another.

This post was rewritten from scratch in September 2026. The original, published on 2012-08-14, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.