DBMS · Keys and constraints

Primary key vs foreign key

Easykeysprimary keyforeign key

Interview question

What is the difference between a primary key and a foreign key?

How to answer

The short answer

A identifies each row of a table. No two rows can share the same primary key value, and the value cannot be NULL. A table has at most one primary key, although that key can be made of more than one column.

A is a column in one table that points at the primary key of another table. It is how two tables are linked, and it stops you from storing a link to something that does not exist.

SQL
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(100)
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    amount      DECIMAL(10, 2)
);

Here customer_id in orders is a foreign key. The database refuses to insert an order for customer 99 if no customer 99 exists, and it refuses to delete a customer who still has orders, unless you chose a rule such as ON DELETE CASCADE.

Differences to remember

  • A primary key must be unique and NOT NULL. A foreign key can repeat, because one customer has many orders, and it can be NULL if the link is optional.
  • A table has one primary key but can have several foreign keys.
  • The primary key protects the table itself. The foreign key protects the relationship between tables.

Interview shortcut

> Primary key = who am I. Foreign key = who do I belong to.

A common follow-up is the difference between a primary key and a unique key. A unique key also forbids repeats, but a table can have several of them, and how NULLs are treated in a unique column differs between databases.