Primary, foreign & unique keys
Overview
Keys establish identity and relationships. Primary keys uniquely identify rows (e.g. `customers.id`). Think of a Primary Key as a row's unique passport number. Foreign keys link rows between tables (e.g. `orders.customer_id` refers to `customers.id`). Think of a Foreign Key as writing someone else's passport number on your form to point to them. Unique keys prevent duplicate values in columns that aren't the primary key.
Without keys, databases would just be disconnected lists. Keys ensure data integrity and allow us to safely join tables together.
Where used: table creation, JOIN operations, data integrity checks
Why learn this
- You can confidently join tables without creating accidental duplicate rows.
- You understand how relational databases enforce relationships.
Query walkthrough
SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'cancelled';Focus: The foreign key relationship allows us to retrieve the customer name for a specific order status.
Common mistakes
- Joining on wrong columns: Joining columns that have matching names but aren't related by a foreign key constraint. Always join a foreign key to its corresponding primary key.
Glossary
- identity
- The unique characteristic that distinguishes one specific row of data from all others.
- enforce
- To make sure that rules or constraints are followed strictly by the database system.
- relational
- A type of database system that stores data in tables which are linked to each other by shared keys.
Recall questions
- What is the difference between a primary key and a foreign key?
Understanding checks
What rows does this return?
order_id: 4, name: Carlos
The foreign key `customer_id` in orders links to `id` in customers. Order 4 belongs to customer 3 (Carlos).
What rows does this return?
total: 2
We are counting how many times the foreign key value 1 appears in the orders table, representing orders for Alice.
Practice tasks
Join Order Items
Given this query, change it to join the `products` table using the foreign key in `order_items`, and select the order item `id` and product `name`.
Challenge
Find Employees by Manager
Write a query that uses the self-referencing foreign key in the `employees` table to find the names of employees managed by 'Sara' (manager_id = 1). Order by name.
Continue learning
Next: Normalization (1NF–3NF)
Next: Constraints & defaults
Next: Indexes — what & when