By the end of this lesson you can identify a table's primary key and foreign keys, look up a row by its key, and follow a foreign key to the row it points to.
Why it matters: Every real database is a web of tables linked by keys. Following a key (from an order to its customer, from a payment to its order) is how you answer almost any business question, and it's the idea behind every join you will write.
Dataset: Starter shop. Examples run in the editor on the right.
Two customers can share a name, a city, even a birthday. So how does a database tell rows apart, and how does an order know which customer placed it? With keys.
Primary key: the row's identity
A primary key is a column (occasionally a set of columns) whose value is unique in the table and never NULL. It is the row's permanent identity, like a national ID number or an invoice number.
In Corner Market, each table's primary key is its _id column: customers.customer_id, products.product_id and orders.order_id. The database enforces this: if anyone tried to insert a second customer with customer_id 7, PostgreSQL would refuse.
Looking a row up by its key
To fetch one specific row, add a WHERE clause after FROM. It keeps only the rows for which a condition is true. You'll study WHERE in depth in the next track; here you need only its simplest form, column equals value:
SELECT *
FROM table_name
WHERE key_column = value;SELECT *
FROM customers
WHERE customer_id = 4;Because customer_id is the primary key, at most one row can match, and here exactly one does: Omar Al-Otaibi. A lookup by primary key always returns zero or one row.
Foreign key: a pointer to another table
Now look at orders. Besides its own key (order_id), each row has customer_id and product_id. These are foreign keys: columns whose values must match a primary key in another table.
orders.customer_id→customers.customer_id(who placed the order)orders.product_id→products.product_id(what was ordered)
The database checks foreign keys too: it won't accept an order for customer 99 if no customer 99 exists. That guarantee is called referential integrity: no pointer leads nowhere.
SELECT *
FROM orders
WHERE order_id = 14;The order row doesn't contain a name or a product title, only numbers: customer_id 4 and product_id 4. To find out who and what, you follow those numbers to customers and products.
One-to-many relationships
A foreign key creates a relationship between two tables. Here it is one-to-many: one customer can have many orders, but each order belongs to exactly one customer. Click the customers in the visual and watch which orders light up. The matching rule is simply equal key values.
Select a customer to see which orders match o.customer_id = c.id.
- #101customer_id 1120
- #102customer_id 180
- #103customer_id 2200
- #104customer_id 560
Noura matches 2 orders, so the join produces 2 rows for this customer.
Notice the difference between the two kinds of key inside orders. order_id is unique, because it's the primary key. customer_id repeats, because one customer can place many orders. The example below proves it with counts: count(*) counts rows, and count(DISTINCT column) counts different values.
SELECT count(*) AS order_rows,
count(DISTINCT order_id) AS different_order_ids,
count(DISTINCT customer_id) AS different_customers
FROM orders;24 rows and 24 different order_ids: the primary key never repeats. But only 11 different customer_ids appear, because several customers ordered more than once and one customer never ordered at all.