Primary Key vs Unique Key vs Foreign Key: What Each Enforces

Primary key vs unique key vs foreign key in one line: a primary key is the one column set that identifies each row, so it must be unique and can never be NULL, and a table has exactly one; a unique key says no two rows may share a value in these columns, a table can have many, and in most engines the columns may hold NULL, several times; a foreign key says a value in this table must exist as a primary or unique key in another table, and it decides what happens to the child rows when the parent is deleted. The first two create an index automatically; the third does not in PostgreSQL and SQLite, and does in MySQL's InnoDB. Engine behaviour is quoted from their manuals, as of October 2026; the SQLite behaviour is run live.

Constraint	Per table	NULL allowed?
PRIMARY KEY	exactly one	never
UNIQUE	as many as you need	yes; several NULLs in SQLite, PostgreSQL, MySQL
FOREIGN KEY	as many as you need	yes; a NULL reference is not checked

The problem it solves

A schema is a set of promises: every photo has one id, no two photographers share an email, no photo claims a photographer who does not exist. Code can try to keep those promises, but every path through the application has to remember them, and the one that forgets corrupts the data quietly. Constraints move the promises into the engine, where every INSERT, UPDATE and DELETE is checked, from every client, forever. The three kinds of key are the three most common promises.

They are also indexes, or depend on one, which is why they live next to the Indexes lesson.

The intuition

Think of the photo library's card catalogue.

• The primary key is the accession number. Each print gets exactly one, no two prints share it, and a print with no number is not in the collection. The library needs exactly one such scheme. Because the engine must check uniqueness on every insert, it builds an index on the key; in many engines that index is also the table's own storage order. In SQLite, INTEGER PRIMARY KEY is an alias for the rowid, so no separate index is created; any other primary key gets an automatic one.
• A unique key is a rule like "one card per email address". It is also enforced by an index, and you can have as many such rules as the data needs. The difference from a primary key is NULL: a photographer with no email on file breaks no rule, and neither does a second one with no email, because NULL is not equal to anything, including another NULL. That is how SQLite, PostgreSQL and MySQL behave. SQL Server is the exception: it treats NULL as a value for this purpose and allows only one NULL per unique constraint. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT for those who want the SQL Server behaviour.
• A foreign key is the box number written on the card. It must name a box that exists, and the engine checks the parent table on every write to the child. It does not say anything about uniqueness in the child: many photos point at one photographer. What it does decide is deletion: ON DELETE RESTRICT or NO ACTION refuses to remove a photographer who still has photos, CASCADE removes the photos too, SET NULL keeps them and blanks the reference. A NULL reference is legal and is not checked at all, which is how "photographer unknown" is stored.

Two details that interviews like:

• Composite keys. Any of the three can span several columns. PRIMARY KEY (box, slot) means the pair is unique, not each column alone.
• Natural against surrogate. A natural key is a real-world identifier, like an ISBN or an email; a surrogate is a generated integer that means nothing outside the database. Surrogates stay stable when the real world changes its mind; natural keys are then kept as unique constraints beside them.

The index question is the one most people get wrong: a primary key and a unique constraint always come with an index, because that is how uniqueness is checked. A foreign key is checked by looking up the parent's key, which already has one; nothing forces an index on the child's referencing column. PostgreSQL's manual recommends adding one, because deleting a parent means searching the child table for references. InnoDB creates it for you if none exists. SQLite does neither.

Watch it run

The animation is the lesson's photo library. WHERE shot_by = 'Okonjo' with no index reads every row from the top. Then CREATE INDEX photo_by builds the second, sorted copy of shot_by, each entry pointing at its row, and the same query becomes a SEARCH. A primary key or unique constraint builds exactly that structure for you, without a CREATE INDEX, because the engine needs it to answer "does this value exist yet?" before every insert. A foreign key on shot_by would not; the query you see here would still be a SCAN until someone creates the index.

The code

The lesson's photo table, now referencing a photographer table. Foreign keys are off by default in SQLite, so the pragma comes first:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")           # off by default in SQLite
db.executescript("""
  CREATE TABLE photographer (
    id    INTEGER PRIMARY KEY,                   -- identity: one per table
    name  TEXT NOT NULL,
    email TEXT UNIQUE                            -- uniqueness: as many as you need
  );
  CREATE TABLE photo (
    id      INTEGER PRIMARY KEY,
    shot_by INTEGER REFERENCES photographer(id) ON DELETE CASCADE,   -- reference
    box     INTEGER
  );
  INSERT INTO photographer VALUES (1, 'Okonjo', 'ok@paper.example'), (2, 'Weiss', NULL);
  INSERT INTO photo VALUES (1, 1, 4), (2, 2, 9), (3, 1, 2);
""")
# UNIQUE created an index; INTEGER PRIMARY KEY is the rowid and needs none; REFERENCES made nothing
print(db.execute("SELECT name, tbl_name FROM sqlite_master WHERE type = 'index'").fetchall())
print(db.execute("EXPLAIN QUERY PLAN SELECT name FROM photographer WHERE email = 'ok@paper.example'").fetchone()[3])
print(db.execute("EXPLAIN QUERY PLAN SELECT box FROM photo WHERE shot_by = 1").fetchone()[3])
# [('sqlite_autoindex_photographer_1', 'photographer')]
# SEARCH photographer USING INDEX sqlite_autoindex_photographer_1 (email=?)
# SCAN photo

# a non-integer primary key gets an automatic index too
db.execute("CREATE TABLE lens (serial TEXT PRIMARY KEY, mount TEXT UNIQUE)")
print(db.execute("SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'lens'").fetchall())
# [('sqlite_autoindex_lens_1',), ('sqlite_autoindex_lens_2',)]

One automatic index, for the UNIQUE email; the integer primary key is the table's rowid and searches it directly. The foreign key column shot_by has no index, so finding a photographer's photos is a SCAN, and so is the check the engine runs when a photographer is deleted. The lens table shows the other case: a text primary key and a unique column each get an automatic index.

Now the rules themselves, one violation at a time. Each attempt prints whether the engine accepted it:

def attempt(sql):
    try:
        db.execute(sql)
        print("ok  ", sql.strip()[:60])
    except sqlite3.IntegrityError as e:
        print("FAIL", sql.strip()[:60], "->", e)

attempt("INSERT INTO photographer VALUES (1, 'Duplicate', 'dup@paper.example')")
attempt("INSERT INTO photographer VALUES (3, 'Second Okonjo', 'ok@paper.example')")
attempt("INSERT INTO photographer VALUES (3, 'Haddad', NULL)")       # second NULL email
attempt("INSERT INTO photo VALUES (4, 9, 1)")                          # no photographer 9
attempt("INSERT INTO photo VALUES (4, NULL, 1)")                       # NULL reference: allowed
print(db.execute("SELECT COUNT(*) FROM photographer WHERE email IS NULL").fetchone())
# FAIL INSERT INTO photographer VALUES (1, 'Duplicate', 'dup@paper. -> UNIQUE constraint failed: photographer.id
# FAIL INSERT INTO photographer VALUES (3, 'Second Okonjo', 'ok@pap -> UNIQUE constraint failed: photographer.email
# ok   INSERT INTO photographer VALUES (3, 'Haddad', NULL)
# FAIL INSERT INTO photo VALUES (4, 9, 1) -> FOREIGN KEY constraint failed
# ok   INSERT INTO photo VALUES (4, NULL, 1)
# (2,)

db.execute("DELETE FROM photographer WHERE id = 1")                     # cascades to the photos
print(db.execute("SELECT id, shot_by FROM photo ORDER BY id").fetchall())
# [(2, 2), (4, None)]

A duplicate id and a duplicate email are both refused, and SQLite reports both as UNIQUE constraint failed, because a primary key is enforced as a unique index plus NOT NULL. A second NULL email is accepted: two photographers now have no email, and no rule is broken. A photo pointing at photographer 9 is refused; a photo pointing at nobody is accepted. Deleting Okonjo removed photos 1 and 3 with him, because of ON DELETE CASCADE, and left the photo with no photographer alone.

Finally a composite key and the two other delete actions, on a fresh connection:

db2 = sqlite3.connect(":memory:")
db2.execute("PRAGMA foreign_keys = ON")
db2.executescript("""
  CREATE TABLE photographer (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
  CREATE TABLE photo (id INTEGER PRIMARY KEY,
                      shot_by INTEGER REFERENCES photographer(id) ON DELETE SET NULL);
  CREATE TABLE box_slot (box INTEGER, slot INTEGER, photo INTEGER REFERENCES photo(id),
                         PRIMARY KEY (box, slot));          -- composite key: the pair is unique
  INSERT INTO photographer VALUES (1, 'Okonjo');
  INSERT INTO photo VALUES (1, 1), (2, 1);
  INSERT INTO box_slot VALUES (4, 1, 1), (4, 2, 2);
""")
try:
    db2.execute("INSERT INTO box_slot VALUES (4, 1, 2)")             # same box, same slot
except sqlite3.IntegrityError as e:
    print(e)
db2.execute("INSERT INTO box_slot VALUES (9, 1, 2)")                 # same slot, other box: fine
db2.execute("DELETE FROM photographer WHERE id = 1")
print(db2.execute("SELECT id, shot_by FROM photo").fetchall())       # SET NULL kept the photos
try:
    db2.execute("DELETE FROM photo WHERE id = 1")                     # box_slot still points at it
except sqlite3.IntegrityError as e:
    print(e)
# UNIQUE constraint failed: box_slot.box, box_slot.slot
# [(1, None), (2, None)]
# FOREIGN KEY constraint failed

Slot 1 in box 4 cannot be used twice, but slot 1 in box 9 is a different pair. Deleting the photographer this time blanked shot_by instead of removing the photos. Deleting photo 1 is refused because a box_slot row still references it and no action was specified, which means NO ACTION: the parent cannot go while a child points at it.

The complexity

Every constraint is a check on every write:

• Primary key and unique: one index lookup per inserted or updated row, O(log n), plus the index maintenance itself. Reads that filter on the key get the same O(log n) search for free.
• Foreign key on insert or update of the child: one lookup in the parent's key index, O(log n).
• Foreign key on delete or key update of the parent: a search of the child table for references. With an index on the referencing column that is O(log n) per child; without one it is a full scan of the child table, per deleted parent row.

Where it goes wrong

• No index on the foreign key column. Deleting one parent scans the whole child table, and the join everyone writes, parent to children, scans it too. PostgreSQL and SQLite leave this to you.
• Treating UNIQUE as NOT NULL. It is not. If the column must be filled in, say NOT NULL as well.
• Expecting one NULL per unique column. SQLite, PostgreSQL and MySQL allow any number; only SQL Server stops at one. Code ported between engines breaks on exactly this.
• ON DELETE CASCADE on the wrong relationship. Deleting a photographer should perhaps not delete the photos. Cascades belong where the child has no meaning without the parent.
• Forgetting PRAGMA foreign_keys = ON in SQLite. Without it the REFERENCES clause is parsed and ignored, per connection.
• A natural key that changes. An email as primary key means every child row changes when the user changes their address. Use a surrogate and keep the email unique.

When it shows up in interviews

As a schema question: "design the tables for photographers and photos", where the interviewer wants the primary keys named, the email made unique, and the foreign key with a deliberate ON DELETE choice. The follow-ups are the NULL behaviour of unique columns and whether a foreign key creates an index. The index that a primary key gives you, and what it costs on every write, is in SQL indexes explained; whether that index is the table itself is in clustered vs non-clustered index; the transaction that keeps a multi-row write consistent is in ACID transactions explained.

How to say it in an interview

"A primary key identifies the row: unique, never NULL, one per table, backed by an index and often the table's storage order. A unique key is any other no-duplicates rule; a table can have several, each backed by an index, and the columns may be NULL, with several NULLs allowed in most engines. A foreign key says this value must exist as a key in the parent table, and its ON DELETE action says what happens to the children when the parent goes. It does not create an index in PostgreSQL or SQLite, so I add one on the referencing column."

Frequently asked questions

What is the difference between a primary key and a unique key?

Both forbid duplicate values and both are enforced by an index. A primary key also forbids NULL, identifies the row, and a table can have only one. A unique key allows NULL, usually several NULLs, and a table can have as many unique keys as it needs.

Does a foreign key create an index?

Not in PostgreSQL or SQLite: the parent's key is indexed already, and nothing is built on the child's referencing column unless you create it. MySQL's InnoDB creates an index on the referencing column automatically if none exists. Add one yourself elsewhere, or deletes of parent rows scan the child table.

Can a unique key have NULL values?

Yes. In SQLite, PostgreSQL and MySQL a unique column may hold NULL in any number of rows, because NULL is not equal to another NULL. SQL Server treats NULL as a value and allows only one. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to get the single-NULL behaviour on request.

Sources

• Constraints — PostgreSQL Documentation
• FOREIGN KEY Constraints — MySQL 8.4 Reference Manual
• Unique constraints — SQL Server documentation
• CREATE TABLE, constraints — SQLite Documentation
• SQLite Foreign Key Support — SQLite Documentation