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.

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:
| id | name | |
|---|---|---|
| 1 | Ada | ada@example.com |
| 2 | Grace | grace@example.com |
| 3 | Linus | linus@example.com |
The orders table:
| id | customer_id | total |
|---|---|---|
| 101 | 1 | 24.00 |
| 102 | 1 | 9.50 |
| 103 | 3 | 42.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.idis the primary key ofcustomers.orders.idis the primary key oforders.orders.customer_idis a foreign key. It points atcustomers.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
1appears many times incustomer_id. - Can sometimes be null, if the relationship is optional.
- Can appear more than once per table. An order could have a
customer_idand astore_id, each pointing somewhere different.
Primary key vs foreign key at a glance
| Primary key | Foreign key | |
|---|---|---|
| Purpose | Identifies a row in its own table | Links a row to a row in another table |
| Unique? | Yes, always | No, values can repeat |
| Null allowed? | No | Yes, unless you add NOT NULL |
| How many per table? | One | As many as you need |
| Example | customers.id | orders.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 failedDeleting 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;| id | name | total |
|---|---|---|
| 101 | Ada | 24.00 |
| 102 | Ada | 9.50 |
| 103 | Linus | 42.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
idthat never changes. - Mismatched types. If
customers.idis an integer,orders.customer_idshould be an integer too. - Creating the child table first.
ordersreferencescustomers, socustomershas to exist before you createorders. - 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_idgoes inorders, not a list of order ids incustomers. - Joining on the wrong columns.
ON orders.id = customers.idruns 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 KEYcreates 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.







