Primary Key vs Foreign Key: The Glue of Relational Databases

If you've ever worked with a relational database, you've heard these two terms constantly. They're foundational — and once you understand how they work together, the whole idea of "relational" databases starts to click.

Primary Key

"Every row needs a unique identity. That's the primary key."

A primary key is a column (or combination of columns) that uniquely identifies each row in a table. No two rows can have the same primary key value, and it can never be NULL.

Think of it like a passport number — everyone has one, no two are the same, and it's how you're uniquely identified.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

Here, user_id is the primary key. Every user gets a unique ID.

Properties of a Primary Key

• Unique — no duplicates allowed.
• Not NULL — every row must have a value.
• One per table — a table can only have one primary key (though it can be composite — made of multiple columns).

Composite Primary Key

Sometimes a single column isn't enough to uniquely identify a row. You can combine two columns:

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

The combination of order_id + product_id is unique — one order can have multiple products, and one product can be in multiple orders. But the pair is unique.

Foreign Key

"A foreign key is how one table references another."

A foreign key is a column in one table that points to the primary key in another table. It's how you create relationships between tables.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  user_id INT,
  order_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Here, user_id in orders is a foreign key that references user_id in the users table. This means: every order must belong to a user that actually exists.

What Does a Foreign Key Actually Do?

It enforces referential integrity — which is just a fancy way of saying: "you can't create an order for a user that doesn't exist, and you can't delete a user who still has orders."

How They Work Together

Let's say we have these two tables:

users:

orders:

The user_id column in orders is a foreign key pointing to users.user_id. So:

• You can't insert an order with user_id = 99 (no such user exists).
• You can't delete user 1 (Lokesh) while orders 101 and 103 still exist — unless you set up cascade rules.

Cascade Options

When a referenced row is deleted or updated, you can tell the database what to do with the dependent rows:

FOREIGN KEY (user_id) REFERENCES users(user_id)
  ON DELETE CASCADE   -- delete orders when user is deleted
  ON UPDATE CASCADE   -- update orders if user_id changes

Key Differences at a Glance

Primary and foreign keys are what make a database relational. Without them, you'd just have a bunch of isolated spreadsheets. Together, they create a structured, connected, and consistent data model where everything references everything else cleanly.