Beginner

Primary Key vs Foreign Key in SQL Explained

Keys are how a relational database keeps data correct. Learn what primary and foreign keys do, how they link tables, what referential integrity buys you, and the ON DELETE options.

Keys are what make a database relational rather than a pile of spreadsheets. A primary key uniquely identifies each row in a table; a foreign key links a row to a row in another table. Together they keep your data consistent and your JOINs meaningful. Keep the SQL cheat sheet open as you read.

Why Keys Exist

Without keys, nothing stops two customers from sharing an id, or an order from pointing at a customer who does not exist. Keys turn those "should never happen" rules into constraints the database enforces for you — so bad data is rejected at write time instead of corrupting reports later.

Primary Keys

A primary key is a column (or set of columns) whose value uniquely identifies a row. It must be unique and cannot be NULL. Each table has at most one primary key, and the database automatically creates an index on it for fast lookups.

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name  text NOT NULL
);

Foreign Keys

A foreign key is a column that references the primary key of another table. It records a relationship — this order belongs to that user — and the database guarantees the referenced row actually exists.

CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id),
  total   numeric(10,2) NOT NULL
);

Primary key ↔ foreign key — A foreign key points at the primary key of another table. The database guarantees every reference resolves to a real row.

Referential Integrity

Referential integrity is the promise that every foreign key value points at a real row. With the constraint in place, the database rejects an insert that references a missing user, and refuses to delete a user who still has orders — unless you tell it what to do about those orders.

-- Rejected: there is no user 9999
INSERT INTO orders (user_id, total) VALUES (9999, 50.00);
-- ERROR: insert violates foreign key constraint

-- Rejected: user 1 still has orders
DELETE FROM users WHERE id = 1;
-- ERROR: update or delete violates foreign key constraint

ON DELETE & ON UPDATE

You decide what happens to child rows when a parent is deleted or its key changes. ON DELETE CASCADE removes the children too; ON DELETE SET NULL clears the link; the default (RESTRICT / NO ACTION) blocks the delete while children exist.

-- Delete a user, and their orders go with them
user_id bigint NOT NULL
  REFERENCES users(id) ON DELETE CASCADE

-- Keep the order but null out the link
manager_id bigint
  REFERENCES employees(id) ON DELETE SET NULL

Composite & Surrogate Keys

A composite key uses more than one column together to identify a row — common in join tables, where the pair of foreign keys is the primary key. A surrogate key is an artificial id (an auto-generated integer or UUID) used instead of natural data like an email, so the key never has to change.

-- Join table: the pair is the primary key (composite)
CREATE TABLE order_items (
  order_id   bigint REFERENCES orders(id),
  product_id bigint REFERENCES products(id),
  quantity   int NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Common Mistakes & Tips

  • Forgetting to index foreign key columns — PostgreSQL does not do it automatically, and JOINs suffer
  • Using a natural key (email) as primary key, then fighting updates when it changes
  • Adding ON DELETE CASCADE without realising how many child rows it can remove
  • Allowing NULL in a foreign key when the relationship is actually required — add NOT NULL
  • Defining no primary key at all, so duplicate rows can creep in

Well-keyed tables are what make SQL trustworthy. Practice on properly related datasets to see primary and foreign keys working together.