SQL Interview Question: Primary Key vs Unique Key vs Foreign Key

Short answer: a primary key uniquely identifies each row and can't be NULL (one per table). A unique key also enforces uniqueness but a table can have many, and they may allow NULLs. A foreign key links a column to a key in another table and enforces that the referenced row exists.

CREATE TABLE customers (
  id     BIGINT PRIMARY KEY,                 -- primary key
  email  VARCHAR(255) NOT NULL UNIQUE,       -- unique key
  phone  VARCHAR(20) UNIQUE                  -- unique, but optional
);

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE RESTRICT
);

Comparison

• How many: one primary key; any number of unique keys and foreign keys.
• NULLs: never in a primary key; unique keys usually allow NULLs (and multiple NULLs are typically allowed, since NULL isn't equal to NULL).
• Indexes: primary and unique keys are backed by indexes automatically. Foreign key columns should be indexed too (MySQL does it automatically; PostgreSQL doesn't).

Foreign key actions

• RESTRICT / NO ACTION - block deleting a customer who has orders.
• CASCADE - delete the orders too.
• SET NULL - keep the orders, clear the reference.

Surrogate vs natural keys

• Surrogate (auto-increment or UUID): stable, meaningless, never needs to change. The usual choice.
• Natural (email, PAN, ISBN): meaningful but can change or be reused; keep them as unique keys instead.

Bonus: a composite key uses several columns, common for join tables like order_items(order_id, product_id).