Two Sams in one library
A library has two members called Sam. A loan says "Sam borrowed Maps". Which Sam?
A name cannot tell them apart. A number can. That number is the key to linking data.
Key words
| Word | What it means | Example |
|---|---|---|
| Primary key | a column whose value is different in every row | member_id |
| Foreign key | a column that holds a primary key from another table | |
| Join | matching rows from two tables by a shared value | |
| Orphan row | a row whose foreign key matches nothing | |
| Table | rows and columns holding one kind of thing | |
| Relational database | tables linked by values they share |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can use a primary key and a foreign key to join two tables.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
A primary key names one row
A primary key is a column where no two rows have the same value. It also never stays empty.
In the members table, member_id is the primary key. Two Sams get two different numbers.
A foreign key points at another table
A foreign key holds a primary key from another table. In loans, member_id points at a row in members.
A join follows that pointer. It puts the loan and the member on one line.
Try it: join loans to members
select loans.loan_id, members.name, loans.title
from loans
join members on members.member_id = loans.member_id
order by loans.loan_id;
-- Look at the result. Loan 103 is missing. Why?Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.