Primary Keys and Foreign Keys

Build the mental model

A primary key is the value that uniquely identifies one specific row in a table — no two rows share it, and it's never left empty. In a users table, users.id = 42 always means exactly one user, forever.

Neither is automatically better: natural keys can change (people change emails), while surrogate keys are stable but carry no business meaning on their own. Real systems often use both patterns together.

A foreign key is different: it's a column in one table that stores the primary key value of a row in another table, creating a link. orders.user_id is expected to match some row's id in the users table.

Primary Key

The value that uniquely identifies one row in a table; never empty, never duplicated.

Foreign Key

A column that stores another table's primary key value, creating a link between two rows.

Surrogate Key

A value generated purely for identification (an auto-incrementing integer or UUID), with no meaning outside the database.

FOREIGN KEY LINK: ORDERS TO USERS
---------------------------------
FOREIGN KEY LINK: ORDERS TO USERS
------------------------------------
   users                    orders
+----+-------+     +-----+---------+----------+
| id | name  |      | id  | user_id | item     |
+----+-------+     +-----+---------+----------+
| 1  | Nilar |      | 101 |    2    | Keyboard |
| 2  | Zaw   |      | 102 |    1    | Monitor  |
| 3  | Hnin  |      | 103 |    3    | Headset  |
+----+-------+      +-----+---------+----------+

  users.id (primary key)  <-  orders.user_id (foreign key)

Connect it to a real scenario

When modeling any new feature, ask two separate questions: 'what uniquely identifies one row here?' (a primary key candidate) and 'does this row need to point back to a row somewhere else?' (a foreign key candidate).

A frequent real-world tension: use an email as a primary key, or generate a surrogate id instead? Many production systems choose the surrogate id so a later email change doesn't ripple into every referencing table.

• A primary key uniquely identifies one row and is never empty or duplicated
• A foreign key stores another table's primary key value to link two rows together
• Natural keys (email) and surrogate keys (generated id/UUID) are two valid primary key strategies

Try the working example

const users = [
  { id: 1, name: "Nilar" },
  { id: 2, name: "Zaw" },
  { id: 3, name: "Hnin" },
];

const orders = [
  { id: 101, user_id: 2, item: "Keyboard" },
  { id: 102, user_id: 1, item: "Monitor" },
  { id: 103, user_id: 3, item: "Headset" },
];

function findOrderOwner(orderId, ordersTable, usersTable) {
  const order = ordersTable.find((o) => o.id === orderId);
  if (!order) return null;
  const user = usersTable.find((u) => u.id === order.user_id);
  return user ? { item: order.item, owner: user.name } : null;
}

console.log(findOrderOwner(101, orders, users));
console.log(findOrderOwner(103, orders, users));
console.log(findOrderOwner(999, orders, users));

{ item: 'Keyboard', owner: 'Zaw' }
{ item: 'Headset', owner: 'Hnin' }
null

Wikipedia: Foreign key — How Databases Work