Keys and Choosing a Primary Key: Candidate, Natural, Surrogate and Composite Keys

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, Cumulative Update 10), and the example in the PostgreSQL note on PostgreSQL 18.6.

Guess First: Can the Same Book Go In Twice When There Is a Primary Key?

The requirement list in the first post of the series was clear on this: no two books can carry the same ISBN (International Standard Book Number). The team builds the books table out of the habit "every table has an id" and gives the primary key to an IDENTITY column. The catalogue team saves Dune; an hour later another screen saves the same book again. Guess: does the second INSERT fail?

CREATE TABLE dbo.books (
    book_id    INT IDENTITY(1,1) NOT NULL,
    isbn       CHAR(13)          NOT NULL,
    title      NVARCHAR(200)     NOT NULL,
    list_price DECIMAL(10,2)     NOT NULL,
    CONSTRAINT PK_books PRIMARY KEY (book_id)
);
INSERT INTO dbo.books (isbn, title, list_price) VALUES ('9798900000268', N'Dune', 390.50);
INSERT INTO dbo.books (isbn, title, list_price) VALUES ('9798900000268', N'Dune', 390.50);
SELECT book_id, isbn, title FROM dbo.books;

(1 rows affected)

(1 rows affected)
book_id  isbn           title
-------  -------------  -----
1        9798900000268  Dune
2        9798900000268  Dune

(2 rows affected)

It didn't. The table has a primary key and it does its job: 1 and 2 are different values. But what the primary key protects is a number the database generated itself. Nothing protects the business rule "one ISBN, once". From now on the stock will be split over two rows, some reviews will be written to 1 and some to 2, and Dune will show up twice in the sales report. When you notice the mistake and try to add the rule afterwards:

ALTER TABLE dbo.books ADD CONSTRAINT UQ_books_isbn UNIQUE (isbn);

Msg 1505, Level 16, State 1, Server b8be943222fa, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.books' and the index name 'UQ_books_isbn'. The duplicate key value is (9798900000268).
Msg 1750, Level 16, State 1, Server b8be943222fa, Line 1
Could not create constraint or index. See previous errors.
The statement has been terminated.

The constraint cannot be added, because the data already violates the rule. First you have to find the duplicates, decide which one to keep, move the dependent rows (order items, reviews, stock) to the kept id, and only then add the constraint:

SELECT isbn, COUNT(*) AS copies, MIN(book_id) AS keep_book_id
FROM dbo.books
GROUP BY isbn
HAVING COUNT(*) > 1;

isbn           copies  keep_book_id
-------------  ------  ------------
9798900000268  2       1

(1 rows affected)

This post is about keys: the chain from superkey to candidate key, natural key to surrogate key and composite key, the traps of key candidates that look "natural", and the physical type decision.

Superkey, Candidate Key, Primary Key and Alternate Key

Every set of attributes that on its own tells the rows of a table apart is a superkey. In the customers table {customer_id} is a superkey, {email} is a superkey, and {customer_id, email} is a superkey too, because adding attributes to a superkey does not stop it from being distinguishing. But the last set is bigger than necessary: drop email from it and it still distinguishes.

A candidate key is a minimal superkey: remove any attribute and it no longer distinguishes. customers has two candidate keys: {customer_id} and {email}. You pick one of them as the primary key; the candidate keys not picked are called alternate keys. An alternate key is not lower than the primary key in the relational model; it is just not the key that foreign keys refer to. In the physical model an alternate key becomes a UNIQUE constraint, and it must.

One more thing: you find candidate keys by looking at the business rule, not at the data. The fact that today no two customers in the table share a name does not make {first_name, last_name} a candidate key. This distinction comes back with functional dependencies in the 5th post of the series.

The Properties of a Good Primary Key

When choosing a primary key you look at five properties. Unique: different for every row. Never null: present for every row; an attribute whose value will be learned later cannot be a key. Minimal: carries no extra columns. Stable: does not change once assigned, because the moment it changes every foreign key that refers to it has to change too. And preferably meaningless: it carries no business information, because business information changes.

The last two properties are the heart of the natural key versus surrogate key debate. A natural key is an identifier that already exists in the business world: ISBN, email, tax number. A surrogate key is a value generated by the database that has no meaning in the outside world: a number from IDENTITY or a GUID (Globally Unique Identifier). A natural key seems to meet the first four properties, but it often fails the "stable" and "never null" tests.

Natural Key Candidates and Their Traps

ISBN looks like the most natural key for a book. But there are several problems. First, not every book has an ISBN. The system was born at the end of the 1960s from the 9-digit SBN (Standard Book Number) in the United Kingdom and was published as the ISO (International Organization for Standardization) 2108 standard in 1970; a second-hand book printed before that, a notebook the bookshop produces itself or a publication that never got an ISBN has no such number. Second, the format changed: since 1 January 2007 ISBNs have 13 digits, before that they had 10. Data coming from an old system may be partly 10 and partly 13 digits, and the same book may show up with two different values. Third, and most important for modeling: an ISBN identifies an edition and a format, not a work. The hardcover, paperback and e-book versions of the same book have different ISBNs. When you make ISBN the key, you have actually answered the question "What does a row of the books table represent?": a work, or a product being sold? Trying to choose a natural key is the fastest way to clarify an entity's grain.

If we are going to accept books without an ISBN, the column becomes nullable and the rule becomes "if there is an ISBN, it is unique". In SQL Server a UNIQUE constraint on a nullable column allows a single NULL; the solution is a filtered unique index. The details of this behaviour are in the "Constraint'ler ve Şema Değişikliği" post of the SQL Temelleri series; here we only build its counterpart in the model:

ALTER TABLE dbo.books DROP CONSTRAINT UQ_books_isbn;
ALTER TABLE dbo.books ALTER COLUMN isbn CHAR(13) NULL;
CREATE UNIQUE INDEX UX_books_isbn ON dbo.books (isbn) WHERE isbn IS NOT NULL;

INSERT INTO dbo.books (isbn, title, list_price) VALUES (NULL, N'Meditations (1890 print)', 2400.00);
INSERT INTO dbo.books (isbn, title, list_price) VALUES (NULL, N'Bookstore Gift Notebook', 150.00);
INSERT INTO dbo.books (isbn, title, list_price) VALUES ('9798900000268', N'Dune', 390.50);

(1 rows affected)

(1 rows affected)
Msg 2601, Level 14, State 1, Server b8be943222fa, Line 7
Cannot insert duplicate key row in object 'dbo.books' with unique index 'UX_books_isbn'. The duplicate key value is (9798900000268).
The statement has been terminated.

Two products without an ISBN went in, the repeated ISBN did not. The model now says "a product may have no ISBN, but two products with an ISBN cannot carry the same number".

Email is a natural key candidate for a customer. The requirement list in the first post, however, is clear: a customer can change their email. If you make the email the primary key, an address change means a key update in orders, reviews, payments and every table that points to the customer. There is a comparison problem as well:

CREATE TABLE dbo.customers (
    customer_id INT IDENTITY(1,1) NOT NULL,
    email       VARCHAR(254)      NOT NULL,
    first_name  NVARCHAR(100)     NOT NULL,
    CONSTRAINT PK_customers PRIMARY KEY (customer_id),
    CONSTRAINT UQ_customers_email UNIQUE (email)
);
INSERT INTO dbo.customers (email, first_name) VALUES ('ada@example.com', N'Ada');
INSERT INTO dbo.customers (email, first_name) VALUES ('Ada@Example.com', N'Ada');
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS db_collation;

(1 rows affected)
Msg 2627, Level 14, State 1, Server b8be943222fa, Line 9
Violation of UNIQUE KEY constraint 'UQ_customers_email'. Cannot insert duplicate key in object 'dbo.customers'. The duplicate key value is (Ada@Example.com).
The statement has been terminated.
db_collation
----------------------------
SQL_Latin1_General_CP1_CI_AS

(1 rows affected)

Because the database collation is case-insensitive, Ada@Example.com and ada@example.com were treated as the same value. In this example that is the behaviour we want; but the moment you match this data against a system that compares case-sensitively (another database, a cache key, a dictionary), two different customers appear. The right place: customer_id as the surrogate primary key, and email as an alternate key protected by a UNIQUE constraint. The identity stays fixed, the email can change and cannot repeat. How collation affects comparison is in the 3rd post of the SQL Temelleri series.

The Turkish national ID number (T.C. kimlik numarası) is another tempting candidate: 11 digits, different for everyone, never changes. But it has three problems. Foreign customers do not have one, so it fails the "never null" test. The ID number is personal data under KVKK (Kişisel Verilerin Korunması Kanunu, the Turkish Personal Data Protection Law No. 6698, published in the Official Gazette on 7 April 2016); the moment you make it the primary key it is copied into every foreign key column, every log line, maybe URLs (Uniform Resource Locators) too, and the answer to "Who keeps this data, and where?" becomes the whole schema. Third, when the purpose of processing personal data ends, an obligation to delete or anonymise it can arise; deleting a value that is a key affects every row that depends on it. If the ID number is needed, it should live in a separate table with restricted access, as an attribute tied to the surrogate key.

Keeping the Natural Key Protected When You Use a Surrogate Key

The main message of this post is this: a surrogate key does not replace the natural key; it carries its load. The surrogate key is the stable identity that foreign keys refer to. The natural key is the uniqueness the business rule states, and it keeps being protected by a UNIQUE constraint. The mistake at the start of this post was forgetting the business rule once a surrogate key was added.

If a table has no natural uniqueness, that is a warning sign too. In a table whose rows are told apart only by IDENTITY, nothing stops the same real-world thing from being saved twice. This will be the first question of the schema review checklist in the last post of the series: "Does this table have a meaningful uniqueness other than IDENTITY?"

Composite Keys: A Natural Choice and Its Cost

For an associative entity a composite key is often the natural choice. For book_authors, (book_id, author_id) expresses both the business rule (an author is linked to a book once) and the identity at the same time; adding a separate book_author_id column would still require a UNIQUE on the same two columns to protect this rule. But when another table starts referring to this table, the cost becomes visible. Let's add a table that keeps authors' royalty shares:

CREATE TABLE dbo.authors (
    author_id INT IDENTITY(1,1) NOT NULL,
    full_name NVARCHAR(200)     NOT NULL,
    CONSTRAINT PK_authors PRIMARY KEY (author_id)
);
CREATE TABLE dbo.book_authors (
    book_id      INT     NOT NULL,
    author_id    INT     NOT NULL,
    author_order TINYINT NOT NULL,
    CONSTRAINT PK_book_authors PRIMARY KEY (book_id, author_id),
    CONSTRAINT FK_book_authors_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id),
    CONSTRAINT FK_book_authors_authors FOREIGN KEY (author_id) REFERENCES dbo.authors (author_id)
);
CREATE TABLE dbo.royalty_shares (
    book_id       INT          NOT NULL,
    author_id     INT          NOT NULL,
    valid_from    DATE         NOT NULL,
    share_percent DECIMAL(5,2) NOT NULL,
    CONSTRAINT PK_royalty_shares PRIMARY KEY (book_id, author_id, valid_from),
    CONSTRAINT FK_royalty_shares_book_authors FOREIGN KEY (book_id, author_id)
        REFERENCES dbo.book_authors (book_id, author_id)
);
INSERT INTO dbo.authors (full_name) VALUES (N'Frank Herbert'), (N'Terry Pratchett');
INSERT INTO dbo.book_authors (book_id, author_id, author_order) VALUES (1, 1, 1);
INSERT INTO dbo.royalty_shares VALUES (1, 1, '2026-01-01', 10.00);
INSERT INTO dbo.royalty_shares VALUES (1, 2, '2026-01-01', 5.00);

(2 rows affected)

(1 rows affected)

(1 rows affected)
Msg 547, Level 16, State 1, Server b8be943222fa, Line 26
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_royalty_shares_book_authors". The conflict occurred in database "dm_04", table "dbo.book_authors".
The statement has been terminated.

The composite foreign key did exactly what we wanted: Terry Pratchett is not among Dune's authors, so no Dune royalty share could be written for him. Two separate foreign keys (one to books, one to authors) could not catch this; each value is valid on its own. The cost is this: royalty_shares carries both columns, every table that points to it will carry them too, every join will be written with two conditions, and if part of the parent key changes the update chain grows longer. The rule: if a single table refers to the composite key and the reference carries a business rule (as above), a composite key is a good choice. If many tables will refer to it, adding a surrogate key and protecting the composite as UNIQUE is often more comfortable.

The Physical Type Decision

Once you have decided on a surrogate key, you need to choose its type and where it is generated. A short decision table:

The effect of these options on the clustered index (page splits, fragmentation) is in the "Page'ler, Heap ve Clustered Index" post of the SQL Server Derinlemesine series; separating the externally exposed id from the internal key, and the fact that UUIDv7's time order does not carry over to the index because SQL Server sorts uniqueidentifier starting from the last 6 bytes, were measured in the "Id'yi Dışarıya Açmak" post of the Çözüm Desenleri series; generation algorithms such as UUIDv7 and Snowflake are in the "Unique ID Üretimi" post of the System Design series. Microsoft's documentation also warns about NEWSEQUENTIALID(): it can only be used inside a DEFAULT constraint, it can continue from a lower range after the server restarts, and because the next value can be guessed it should not be used where privacy matters.

PostgreSQL 18 note: with PostgreSQL 18 the uuidv7() function arrived in core and can be used directly as a DEFAULT. Because the uuid type is compared byte by byte from start to end, the time prefix carries over to the index order in PostgreSQL:

CREATE TABLE gift_cards (
    gift_card_id uuid PRIMARY KEY DEFAULT uuidv7(),
    code         varchar(20) NOT NULL UNIQUE
);
INSERT INTO gift_cards (code) VALUES ('GIFT-0001'), ('GIFT-0002');
SELECT gift_card_id, uuid_extract_version(gift_card_id) AS version,
       uuid_extract_timestamp(gift_card_id) AS created_at, code
FROM gift_cards
ORDER BY gift_card_id;

CREATE TABLE
INSERT 0 2
             gift_card_id             | version |         created_at         |   code
--------------------------------------+---------+----------------------------+-----------
 01a1d6d1-58a5-7bd5-9e65-0e76445f4659 |       7 | 2026-11-13 21:30:06.373+00 | GIFT-0001
 01a1d6d1-58a5-7d84-b99e-c46151562ee1 |       7 | 2026-11-13 21:30:06.373+00 | GIFT-0002
(2 rows)

The two values generated in the same millisecond have the same timestamp, but their order is consistent with the order of generation. The PostgreSQL-side details of this behaviour are in the PostgreSQL series.

In Production

Put the constraint in from the start. The scenario at the beginning of this post shows that once duplicate data is in, adding a constraint turns into a clean-up project: find the duplicates, pick one, move the dependent rows, then add the constraint. Doing this clean-up live also requires guaranteeing that no new duplicates come in meanwhile.

Watch the INT limit. INT IDENTITY stops at 2,147,483,647 and the next insert fails. On fast-growing tables, tracking how much of the identity range is used (IDENT_CURRENT) and planning the move to BIGINT months before the limit is far cheaper than stopping suddenly one night. The move also means changing the type of every foreign key column that refers to that key.

Make the decision to expose the surrogate key separately. An internal key that is a sequential number brings with it /orders/1003 in URLs and competitors guessing your order volume. The ways to separate the externally exposed id from the internal key are in the "Id'yi Dışarıya Açmak" post of the Çözüm Desenleri series.

Normalise the natural key's format. ISBNs can come with or without hyphens, with 10 or 13 digits; emails with different upper and lower case. A UNIQUE constraint only works if the values are stored in the same form. Protect the format rule with a CHECK constraint or with a single normalisation function on the write path.

Common Pitfalls

Adding a surrogate key and forgetting the uniqueness in the business rule entirely. Two books with the same ISBN at the start of this post are the most common form of this; two customers with the same email and two coupons with the same code come from the same mistake.

Making a value that can change the primary key. Email, phone, username, product code: they all change one day, and on that day every foreign key has to be updated.

Making personal data a key. When an ID number or an email becomes a key, it is copied into every corner of the schema; a deletion or anonymisation request affects the whole schema.

Adding a surrogate key to every table by reflex. In an associative entity (book_id, author_id) is already a natural and sufficient key; adding IDENTITY on top means one more column and one more index, and protecting the rule still needs a composite UNIQUE.

Choosing the key by looking at the data. Deciding that a column is a key because it does not repeat today breaks with tomorrow's first duplicate record. The business rule decides the key.

A Small Experiment

★☆☆ Add a nullable phone column to the customers table and build the rule "if there is a phone, it is unique" with a filtered unique index. Try writing the same phone for two customers and leaving two customers without a phone.

★★☆ Decide whether the books table represents a work or a product being sold. If it is a product, design a works table that links the hardcover and e-book versions of the same work. Which table's natural key is the ISBN, and what could the other's natural key be?

★★★ Redesign the royalty_shares table so that it connects to book_authors through a surrogate key: add book_author_id to book_authors and point the foreign key at it. Is the rule that stops a Dune share being written for Terry Pratchett still protected? If not, how do you bring it back?

We have chosen keys, but we have not yet asked systematically which key an attribute in a table depends on. In the next post we move to the second part of the series, normalization: we will load the order spreadsheet into SQL Server as it is, watch insert, update and delete anomalies live, and explain where these anomalies come from with the concept of functional dependency.