Primary key vs foreign key explained for beginners, with SQL examples

Imagine a shop with a list of customers and a list of orders. Each order must say who placed it. You could write the customer's whole name and city on every order, but then Asha's details would be copied again and again. The better way is to give every customer a unique number and write only that number on each order. Those two ideas are exactly a primary key and a foreign key.

This guide builds the 2 tables, then tries to break them. The statements ran in DuckDB, and each error message is exactly what it printed. The ideas are the same in MySQL, PostgreSQL, SQL Server and Oracle.

1Primary key: the unique label for a row

A primary key is a column (or a group of columns) whose value is different for every row, so it can point at exactly 1 row. It has 2 rules: it can never repeat, and it can never be empty (NULL). Here customer_id is the primary key of customers.

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        VARCHAR NOT NULL,
  city        VARCHAR
);

INSERT INTO customers VALUES
  (1, 'Asha', 'Pune'),
  (2, 'Ravi', 'Mumbai');

Try to add a second customer with the same id. The database refuses:

INSERT INTO customers VALUES (1, 'Meena', 'Chennai');

Constraint Error: Duplicate key "customer_id: 1" violates primary key constraint.

And try to add one with no id at all:

INSERT INTO customers VALUES (NULL, 'Meena', 'Chennai');

Constraint Error: NOT NULL constraint failed: customers.customer_id

Both are rejected, which is the point: you can trust that customer_id = 1 always means one person. A good primary key is stable (it never changes) and has no meaning in the real world. A phone number or email looks unique but people change them, so most tables use a plain number.

2Foreign key: a pointer to another table

A foreign key is a column whose values must exist as primary keys in another table. In orders, the column customer_id is a foreign key pointing at customers.customer_id.

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers (customer_id),
  amount      INTEGER
);

INSERT INTO orders VALUES
  (101, 1, 1200),
  (102, 1,  300),
  (103, 2,  800);

SELECT o.order_id, c.name, c.city, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

order_id | name | city   | amount
---------+------+--------+-------
101      | Asha | Pune   | 1200
102      | Asha | Pune   | 300
103      | Ravi | Mumbai | 800
(3 rows)

Each order now finds its customer by following the key. Asha has 2 orders: one customer can have many orders, but each order belongs to exactly one customer. That is called a one-to-many relationship, and it is the most common kind.

3What the foreign key protects you from

First, an order for a customer who does not exist:

INSERT INTO orders VALUES (104, 99, 500);

Constraint Error: Violates foreign key constraint because key "customer_id: 99" does not exist in the referenced table

The database refuses, because there is no customer 99. Without the foreign key you would have an "orphan" order that points at nothing. Second, deleting a customer who still has orders:

DELETE FROM customers WHERE customer_id = 1;

Constraint Error: Violates foreign key constraint because key "customer_id: 1" is still referenced by a foreign key in a different table. If this is an unexpected constraint violation, please refer to our foreign key limitations in the documentation

Refused again, because orders 101 and 102 still point at Asha. You have to deal with her orders first, or tell the database what to do automatically with an ON DELETE rule. Finally, a foreign key column is allowed to be NULL unless you forbid it, which models an order with no known customer:

INSERT INTO orders VALUES (105, NULL, 500);

SELECT order_id, customer_id FROM orders ORDER BY order_id;

order_id | customer_id
---------+------------
101      | 1
102      | 1
103      | 2
105      | NULL
(4 rows)

The row with NULL is allowed. If every order must have a customer, declare the column NOT NULL too.

4Primary key vs foreign key side by side

5Keys in warehouses and data engineering

In a normal application database (MySQL, PostgreSQL), keys are enforced as you saw. In many cloud warehouses they are not. Snowflake and BigQuery let you declare primary and foreign keys but do not stop you inserting duplicates or orphans, because checking keys on huge tables would slow loading down. The declaration still helps tools and humans understand the design, but the checking becomes your job: a data engineer writes a query that looks for duplicate keys and orphan values after each load, as shown in how to remove duplicate rows in SQL.

You will also meet two other words. A surrogate key is a number the system invents as the primary key. A natural key is a real-world value such as an email address used as the key. Data warehouses almost always add surrogate keys, because natural keys can change, which is explained in the SCD guide.

Quick checklist

1. 1Every table gets a primary key. Make it a stable number.
2. 2A column that refers to another table is a foreign key, and its values must exist in that table.
3. 3Join on the key pair: orders.customer_id = customers.customer_id.
4. 4Do not assume a warehouse enforces keys. Test for duplicates and orphans yourself.

Practise following keys with the free problems Employee department names and Customers who never ordered. To see why data is split into linked tables in the first place, read database normalization explained.