DataslopeForeign Keys
The column that turns two separate tables into a connected pair — how a foreign key points at a primary key, and how it protects your data's integrity.
We have hinted at how tables connect: an order stores a customer's
id. That pointing column has a name — a foreign key — and it is
the mechanism that makes the relational model relational. This page
makes it concrete.
A foreign key is a pointer to a primary key
A foreign key is a column in one table that holds the primary key value of a row in another table. It is a reference — a way for one row to say "I belong to that row over there."
In the orders table, customer_id is a foreign key. Its value is
the id of some customer. Order 101 storing customer_id = 1 means
"this order belongs to customer 1 (Ada)."
Why store an id instead of the name?
Because of everything we learned about identity and consistency.
The id is stable and unique; the name is neither. Storing
customer_id = 1 rather than "Ada" means:
- Ada's name lives in exactly one place (the customers table).
- If she changes her name, every order still points correctly, because the id never changed.
- Two customers named "Ada" are still unambiguous — they have different ids.
This is the duplication cure from the Spreadsheets vs. Databases page, now given a precise mechanism.
Foreign keys protect integrity
Declaring a foreign key does more than document intent — PostgreSQL enforces it. It will refuse to create an order that points to a customer who does not exist. This guarantee is called referential integrity: every reference actually points to something real.
The phrase REFERENCES customers(id) is what declares the foreign
key. The first insert succeeds; the second is rejected because there
is no customer 99. The database is preventing an orphan — an
order belonging to nobody.
Why orphans are dangerous
Without referential integrity, you could have orders pointing at customers that were deleted or never existed. Reports would undercount, joins would lose rows, and "show this order's customer" would fail. Foreign keys make such broken links impossible.
The shape of a connected pair
Put the two ideas side by side and the whole relational mechanism appears:
Primary key on one side, foreign key on the other, arrows pointing from the many to the one. Once you can see this picture, you are ready to actually combine the two tables — which is the join, the subject of the next page.
Check your understanding
What is a foreign key?
A second primary key that identifies the same row.
A column that stores the primary key value of a row in another table, creating a reference between them.
A password that locks a table.
A column that must always be unique within its own table.
Why store customer_id on an order instead of copying the customer's name?
Because names cannot be stored in PostgreSQL.
Because the id is stable and unique, so the customer's details stay in one place and references remain correct even if the name changes.
Because an id takes exactly one byte and a name does not.
Because foreign keys are not allowed to be text.
A foreign key from orders.customer_id to customers.id is declared. What does PostgreSQL do if you try to insert an order with customer_id = 99 when no customer 99 exists?
It creates a placeholder customer 99 automatically.
It inserts the order with a NULL customer.
It rejects the insert, because the foreign key requires the referenced customer to exist (referential integrity).
It deletes the orders table to stay safe.
Filtering Groups with HAVING
Why WHERE cannot filter on a SUM or COUNT, and how HAVING lets you keep only the groups that meet a condition.
Understanding Joins
The big picture of combining tables — what a join actually does, why it matches rows on a condition, and how to picture the result before writing one.