Start.dev
Back to blog

Primary key vs foreign key, explained simply

What a primary key and a foreign key are, how they link two tables, and the mistakes beginners make when setting them up in SQL.

Ariel Weinberger
Ariel Weinberger ยท 6 min read

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

Short answer: a primary key is the column that uniquely identifies each row in its own table. A foreign key is a column in another table that stores one of those primary key values, so the two rows are linked.

A primary key says "this is who I am". A foreign key says "this is who I belong to".

This post shows both with two small tables, how to create them in SQL, how they make joins work, and the mistakes that cause confusing errors. If SELECT and WHERE are new, start with SQL for beginners: 10 queries to learn first.

The example: customers and orders

Imagine a small shop. It has customers, and customers place orders.

The customers table:

idnameemail
1Adaada@example.com
2Gracegrace@example.com
3Linuslinus@example.com

The orders table:

idcustomer_idtotal
101124.00
10219.50
103342.00

Look at customer_id. Order 101 and 102 both have 1, so both belong to Ada. Order 103 has 3, so it belongs to Linus. Grace has no orders yet.

  • customers.id is the primary key of customers.
  • orders.id is the primary key of orders.
  • orders.customer_id is a foreign key. It points at customers.id.

What a primary key is

A primary key is the one column, or set of columns, that identifies a row. It has two rules:

  • Unique. No two rows can have the same value.
  • Not null. Every row must have one.

Names make bad primary keys, because two customers can both be called Ada. Emails seem unique, but people change them. That is why most tables use a plain number, like id, that never changes and means nothing else.

Each table has exactly one primary key. Identifying Rows With Primary Key (opens in a new tab) covers it in the course.

What a foreign key is

A foreign key is a column whose values must match a primary key in another table. It is how one table refers to another without copying all its data.

Without it, every order would need to repeat the customer's name and email. If Ada changed her email, you would have to update every order she ever placed. With a foreign key, the email lives in one place, and each order just stores 1.

Unlike a primary key, a foreign key:

  • Can repeat. Ada can have many orders, so 1 appears many times in customer_id.
  • Can sometimes be null, if the relationship is optional.
  • Can appear more than once per table. An order could have a customer_id and a store_id, each pointing somewhere different.

Primary key vs foreign key at a glance

Primary keyForeign key
PurposeIdentifies a row in its own tableLinks a row to a row in another table
Unique?Yes, alwaysNo, values can repeat
Null allowed?NoYes, unless you add NOT NULL
How many per table?OneAs many as you need
Examplecustomers.idorders.customer_id

Creating both in SQL

Here is how you would create those two tables:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);
 
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total NUMERIC NOT NULL
);

PRIMARY KEY marks the identifying column. REFERENCES customers(id) turns customer_id into a foreign key that must match a real customer. You can practise both in Creating A Table With Create Table (opens in a new tab) and Linking Tables With References (opens in a new tab).

What the foreign key protects you from

Once the foreign key is in place, the database checks every change.

This insert fails, because there is no customer 99:

INSERT INTO orders (id, customer_id, total)
VALUES (104, 99, 15.00);
-- error: foreign key constraint failed

Deleting Ada also fails while she still has orders, because those orders would point at nothing. That is the point. The database refuses to create orphan rows that belong to no one.

One caveat: most databases enforce foreign keys by default, but SQLite only does once foreign key support is switched on. If an insert you expected to fail goes through, that is the first thing to check.

How keys make joins work

Keys are what joins match on. To list each order with the customer's name, join the foreign key to the primary key:

SELECT orders.id, customers.name, orders.total
FROM orders
JOIN customers ON orders.customer_id = customers.id;
idnametotal
101Ada24.00
102Ada9.50
103Linus42.00

Almost every join you write will look like this: foreign_key = primary_key. SQL joins explained with simple examples goes further, and Joining Two Tables (opens in a new tab) has the exercise.

Common mistakes

  • Using a name or email as the primary key. Names repeat and emails change. Use an id that never changes.
  • Mismatched types. If customers.id is an integer, orders.customer_id should be an integer too.
  • Creating the child table first. orders references customers, so customers has to exist before you create orders.
  • Putting the foreign key on the wrong side. In a one-to-many relationship, the foreign key goes on the "many" side. Each order has one customer, so customer_id goes in orders, not a list of order ids in customers.
  • Joining on the wrong columns. ON orders.id = customers.id runs without an error but matches order 1 to customer 1, which is meaningless. Join the foreign key to the primary key it references.

Quick recap

  • A primary key uniquely identifies a row in its own table. It can't repeat or be null.
  • A foreign key stores another table's primary key value to link the two rows. It can repeat.
  • PRIMARY KEY creates the first. REFERENCES table(column) creates the second.
  • Foreign keys stop you from inserting or leaving rows that point at nothing.
  • Joins match a foreign key to the primary key it references.

Frequently asked questions

Can a table have more than one primary key?

No. A table has one primary key. That key can be made of more than one column, which is called a composite key, but it is still one key.

Can a foreign key be null?

Yes, unless you add NOT NULL. A null foreign key means the row isn't linked to anything yet, for example an order that hasn't been assigned to a store.

Can a foreign key reference a column that isn't a primary key?

It can reference any column that is guaranteed unique. In practice, it almost always references the primary key.

Is a primary key the same as an index?

Not quite, but databases create an index for the primary key automatically so lookups by id are fast. Foreign key columns often benefit from an index too.

Do I need foreign keys if I always write correct joins?

Joins work without them, but foreign keys stop bad data from getting in. They catch the typo or the forgotten delete before it becomes a bug that is hard to trace.

Practice

Build the tables yourself in Identifying Rows With Primary Key (opens in a new tab) and Linking Tables With References (opens in a new tab), then join them in Joining Two Tables (opens in a new tab). Each exercise runs in your browser, so there is nothing to install. Then try counting orders per customer with SQL GROUP BY.

Keep going

Learn SQL (opens in a new tab) takes you from your first SELECT to designing linked tables and querying them in a small capstone project. When you are ready to use a database from JavaScript, Learn Prisma (opens in a new tab) picks up from there.

Level up your coding skills

Jump into interactive courses and start building real-world projects.

Start learning

Related posts

Confused by SQL joins? Learn INNER, LEFT, RIGHT, FULL and CROSS joins with one tiny dataset, plain-English explanations and copy-paste queries.
Ariel Weinberger
Ariel Weinberger ยท 7 min read
Learn SQL GROUP BY with one small table: count and total rows per group, group by two columns, filter groups with HAVING, and fix the classic GROUP BY error.
Ariel Weinberger
Ariel Weinberger ยท 6 min read
A practical guide to the ten SQL queries that cover most of what beginners need: SELECT, WHERE, ORDER BY, GROUP BY, JOIN, and safe UPDATE and DELETE.
Ariel Weinberger
Ariel Weinberger ยท 4 min read