Index vs Primary Key: The Rule and the Structure That Enforces It

Index vs primary key in one line: a primary key is a constraint, a rule that says every row has a value in these columns, no two rows share it and none of it is NULL, with at most one such rule per table; an index is a physical lookup structure that keeps column values in order so the engine can jump to rows instead of scanning, and it may allow duplicates or forbid them. The two meet because engines enforce a primary key by building a unique index for it. PostgreSQL and MySQL behaviour below is quoted from their manuals, as of October 2026; the SQLite behaviour is run live in Python.

	Primary key	Index
What it is	a constraint: unique and NOT NULL	a structure: sorted entries pointing at rows
How many per table	at most one	as many as you are willing to maintain
Duplicates	never	allowed, unless declared UNIQUE
Created by	PRIMARY KEY in the table definition	CREATE INDEX, or implicitly by a constraint

The problem it solves

Both words show up in the same schema review, often in the same sentence: "add an index on the id", "the id is already the primary key". The confusion costs in two directions. People add a second index on a primary key column that already has one, paying twice on every write for nothing. Or they assume an index makes values unique, and discover duplicates in production because a plain index never promised that.

The intuition

Separate the promise from the machinery.

• A primary key is a promise about the data. It identifies each row: one value per row, never repeated, never missing. It is logical, part of the schema's meaning, and other tables point at it with foreign keys. Uniqueness and NOT NULL together are the whole definition; how they are checked is left to the engine.
• An index is machinery for finding rows. It is a second, sorted copy of some columns with a pointer back to each row. It exists for speed and changes no query result. A plain index is happy to hold three entries for Okonjo; a UNIQUE index refuses the second one.
• The engine connects them. Checking "is this key already taken?" on every insert by scanning the table would be ruinous, so the engine builds a unique index on the key and checks it. PostgreSQL's manual says it automatically creates a unique index when a primary key or unique constraint is defined, and that this index is the mechanism that enforces the constraint, so indexing those columns again only duplicates it.

Where the index lives differs by engine. In MySQL's InnoDB, the primary key is the clustered index: the table's rows are stored in its order, and PRIMARY KEY columns are made NOT NULL implicitly if you did not say so. In SQLite, a column declared exactly INTEGER PRIMARY KEY becomes an alias for the rowid, the key of the table's own B-tree, so no separate index is created; any other primary key gets an automatic one.

When to use which

• Every table gets a primary key. It is the row's identity, the target of foreign keys and, in InnoDB, the storage order. Prefer a short, stable value.
• Add an index for a query, not for a column. Index the columns your WHERE, JOIN and ORDER BY clauses actually use, and only when the read gain beats the write cost.
• Need uniqueness on something that is not the identity, such as an email? Use a UNIQUE constraint (or unique index), not a second primary key; a table cannot have two.
• Never index the primary key column again. It already has one.

Watch it run

The animation is the lesson's newspaper photo library. WHERE shot_by = 'Okonjo' with no index reads the table from the top, row by row, three rows read to return two. Then CREATE INDEX photo_by builds a sorted copy of shot_by, each entry pointing back at its row, and the same query becomes a SEARCH that jumps into the Okonjo block. The last frames show the price: every new print is filed twice, and WHERE box = 9 still scans. Hold that picture for a primary key: it is the same sorted structure, built for you on the key column, with one extra rule that two entries may never be equal.

The code

The lesson's photo table, now with an accession number ref as a text primary key, plus the lesson's ordinary index on shot_by. Which indexes exist, which are unique, where each came from, and what the planner does with them:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE photo (ref TEXT PRIMARY KEY, shot_by TEXT, box INTEGER);
  CREATE INDEX photo_by ON photo(shot_by);
  INSERT INTO photo VALUES ('N-001', 'Okonjo', 4), ('N-002', 'Weiss', 9), ('N-003', 'Okonjo', 2);
""")
print(db.execute("SELECT name FROM sqlite_master WHERE type = 'index'").fetchall())
# [('sqlite_autoindex_photo_1',), ('photo_by',)]
for seq, name, unique, origin, partial in db.execute("PRAGMA index_list(photo)"):
    print(name, "unique" if unique else "not unique", "origin:", origin)
# photo_by not unique origin: c
# sqlite_autoindex_photo_1 unique origin: pk

db.execute("INSERT INTO photo VALUES ('N-004', 'Okonjo', 7)")     # a third Okonjo: fine
try:
    db.execute("INSERT INTO photo VALUES ('N-001', 'Weiss', 1)")  # a second N-001
except sqlite3.IntegrityError as e:
    print(e)                          # UNIQUE constraint failed: photo.ref

plan = lambda q: db.execute("EXPLAIN QUERY PLAN " + q).fetchone()[3]
print(plan("SELECT box FROM photo WHERE ref = 'N-002'"))
# SEARCH photo USING INDEX sqlite_autoindex_photo_1 (ref=?)
print(plan("SELECT box FROM photo WHERE shot_by = 'Weiss'"))
# SEARCH photo USING INDEX photo_by (shot_by=?)
print(plan("SELECT box FROM photo WHERE box = 9"))
# SCAN photo
try:
    db.execute("DROP INDEX sqlite_autoindex_photo_1")
except sqlite3.OperationalError as e:
    print(e)    # index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped

Two indexes, one we wrote and one we did not. PRAGMA index_list reports the automatic one as unique with origin pk, and ours as not unique with origin c, for CREATE INDEX. The plain index accepted a third Okonjo; the primary key's index rejected a second N-001. Both make lookups a SEARCH, and the index cannot be dropped on its own, because the constraint depends on it.

Now the edges: an INTEGER PRIMARY KEY that needs no index, a SQLite quirk with NULL keys, and a seeded check that a primary key accepts exactly what NOT NULL plus a unique index accepts:

import random
import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE print_a (id INTEGER PRIMARY KEY, title TEXT);   -- the rowid itself
  CREATE TABLE print_b (ref TEXT PRIMARY KEY, title TEXT);     -- rowid table, text key
  CREATE TABLE print_c (ref TEXT NOT NULL PRIMARY KEY, title TEXT);
""")
print(db.execute("PRAGMA index_list(print_a)").fetchall())   # []  <- no separate index

db.execute("INSERT INTO print_b VALUES (NULL, 'Harbour'), (NULL, 'Ferry')")
print(db.execute("SELECT * FROM print_b").fetchall())   # [(None, 'Harbour'), (None, 'Ferry')]
try:
    db.execute("INSERT INTO print_c VALUES (NULL, 'Harbour')")
except sqlite3.IntegrityError as e:
    print(e)                                            # NOT NULL constraint failed: print_c.ref

# A primary key behaves like NOT NULL + a unique index. Check it on random inserts.
db.executescript("""
  CREATE TABLE pk_way  (ref TEXT NOT NULL PRIMARY KEY);
  CREATE TABLE idx_way (ref TEXT NOT NULL);
  CREATE UNIQUE INDEX idx_way_ref ON idx_way(ref);
""")
def accepted(table, value):
    try:
        db.execute(f"INSERT INTO {table} VALUES (?)", (value,))
        return True
    except sqlite3.IntegrityError:
        return False

random.seed(7)
ok = True
for _ in range(2000):
    v = random.choice([None, "N-1", "N-2", "N-3", "N-" + str(random.randint(4, 300))])
    ok = ok and accepted("pk_way", v) == accepted("idx_way", v)
print(ok, db.execute("SELECT COUNT(*) FROM pk_way").fetchone())   # True (200,)

print_a has no index at all: its key is the table's own B-tree key. print_b stored two rows whose primary key is NULL. SQLite's documentation calls this a bug kept for compatibility: unless the column is an INTEGER PRIMARY KEY, the table is STRICT or WITHOUT ROWID, or you write NOT NULL, a primary key column accepts NULL. PostgreSQL and MySQL never allow it. And over 2,000 random inserts, the primary key and the NOT NULL unique index agreed on every single one.

The complexity

• Lookup by primary key or by any B-tree index: O(log n) to find the entry; a non-clustered index then fetches the row.
• Insert: O(log n) per index on the table, primary key's included. A primary key adds the uniqueness check to that same descent, so it costs about what a unique index costs.
• Storage: one copy of the indexed columns, plus a row pointer, per index. A redundant index on the primary key column doubles that for no gain.

Where it goes wrong

• Indexing the primary key again. The engine already built one; a second index on the same column only adds write cost and storage.
• Expecting a plain index to block duplicates. Only a UNIQUE index or constraint does that.
• Trusting PRIMARY KEY to mean NOT NULL in SQLite. Declare NOT NULL explicitly, or use STRICT tables.
• Assuming the foreign key side is indexed. The referenced key has an index; the referencing column usually does not, unless you add it. InnoDB is the exception and creates one.
• A wide primary key in InnoDB. Every secondary index stores the primary key value as its row pointer, so a long key makes every index longer.

When it shows up in interviews

Usually as "what is the difference between a primary key and an index?" or "does a primary key create an index?". A strong answer separates constraint from structure and then names what each engine does. Uniqueness rules across the three kinds of keys are in primary key vs unique key vs foreign key, and where the rows physically live is in clustered vs non-clustered index. The scan-versus-search mechanics are in the Indexes lesson.

How to say it in an interview

"A primary key is a constraint: it says each row has a unique, non-null identity, and a table can have only one. An index is a data structure, usually a B-tree, that makes lookups fast; it can be unique or not, and a table can have many. They overlap because engines enforce a primary key with a unique index: PostgreSQL creates one automatically, InnoDB stores the whole table in primary key order, and SQLite uses the rowid for an INTEGER PRIMARY KEY. So I never index the primary key again, and when I need uniqueness on another column, I add a unique constraint."

Frequently asked questions

Does a primary key automatically create an index?

In practice, yes. PostgreSQL creates a unique B-tree index for every primary key. InnoDB uses the primary key as the clustered index, so the table itself is that index. SQLite creates an automatic index, except for INTEGER PRIMARY KEY and WITHOUT ROWID tables, where the table's own B-tree is keyed by it.

Is a primary key the same as a unique index?

Almost. A primary key is unique and NOT NULL, at most one per table, and is the default target of foreign keys. A unique index enforces uniqueness only, usually allows NULL values, and a table can have many. Engines implement the primary key with a unique index underneath.

Should I add an index on the primary key column?

No. The engine already has one, and PostgreSQL's manual says a manual index on those columns would just duplicate it. Index other columns that queries filter, join or sort on, starting with foreign key columns, which are usually not indexed automatically.

Sources

• Unique Indexes — PostgreSQL Documentation
• Constraints — PostgreSQL Documentation
• Clustered and Secondary Indexes — MySQL 8.4 Reference Manual
• CREATE TABLE — MySQL 8.4 Reference Manual
• CREATE TABLE, PRIMARY KEY and ROWID — SQLite Documentation
• FOREIGN KEY Constraints — MySQL 8.4 Reference Manual