SQL Easy Asked at AmazonAsked at GoogleAsked at Microsoft

What is the difference between a primary key and a foreign key, and what guarantees do they provide?

For Data Analyst Data Engineer Data Scientist

The short answer

A primary key uniquely identifies each row in a table and implicitly creates a unique index; it cannot be NULL. A foreign key in a child table references the primary key of a parent table and enforces referential integrity — the database rejects inserts or updates that reference a non-existent parent row, and rejects parent deletes that would orphan child rows unless a cascade rule is defined.

How to think about it

What the question is really testing

This is a relational fundamentals question. Interviewers want to know whether you understand what guarantees these constraints provide at the database level — not just that they exist. The follow-ups usually go to cascade rules and the missing-index trap on FK columns.

The relationship in one diagram

The mental model — PK owns the value, FK borrows it

customers table               orders table
────────────────              ─────────────────────────
id (PRIMARY KEY)  ◄───────── customer_id (FOREIGN KEY)
email                         amount
name                          created_at

FK rule: every orders.customer_id must exist in customers.id
         you cannot insert an order for a customer who doesn't exist
         you cannot delete a customer who still has orders (unless CASCADE)

What each key enforces

CREATE TABLE customers (
  id         INTEGER      PRIMARY KEY,   -- unique, NOT NULL, auto-indexed
  email      TEXT         UNIQUE NOT NULL,
  name       TEXT         NOT NULL
);

CREATE TABLE orders (
  id          INTEGER  PRIMARY KEY,
  customer_id INTEGER  NOT NULL
    REFERENCES customers(id)             -- FK: must exist in customers.id
    ON DELETE CASCADE,                   -- when customer deleted, orders deleted too
  amount      REAL,
  created_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

Property	Primary Key	Foreign Key
Uniqueness	Yes — one row per value	No inherent uniqueness on child side
NOT NULL	Always	You choose (nullable FK = optional relationship)
Auto index	Yes	No in PostgreSQL/Oracle — you must create it manually
Referential integrity	Owns the referenced value	Ensures the referenced value exists

CASCADE options — controlling what happens on parent delete/update

ON DELETE CASCADE      -- delete child rows when parent is deleted
ON DELETE SET NULL     -- set FK to NULL when parent is deleted
ON DELETE RESTRICT     -- (default) block parent delete if children exist
ON UPDATE CASCADE      -- propagate parent PK change to child FK

Pick CASCADE when children are meaningless without the parent (orders without a customer). Pick RESTRICT or SET NULL when children should survive the parent’s deletion.

The missing index trap on FK columns

PostgreSQL does not automatically create an index on a foreign key column — only on the primary key. This catches many engineers off guard:

-- Without this index, a join or cascading delete on customer_id
-- triggers a full sequential scan of the orders table
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

DELETE FROM customers WHERE id = 42 must scan every row of orders to find children to cascade-delete. On a million-row table, that is catastrophically slow without the index.

Surrogates vs. natural keys

Surrogate key: system-generated integer or UUID. Stable, joins are fast, no business-logic leakage.
Natural key: a real business attribute (email, isbn). Meaningful, but can change, may be long (bad join performance), and sometimes nullable.

Production systems typically use surrogate PKs and enforce natural-key uniqueness with a separate UNIQUE constraint.