Member 99 does not exist
Oakfield Youth Club is made up. Its sign-up list says member 99 joined Chess. The club has no member 99.
Somebody typed the wrong number and nothing stopped them. A relationship between tables stops this, and a good form makes it hard to happen.
By the end of this lesson
Note
I can link two tables with a foreign key (a field that points to another table) and say what a form button does.
Key words
| Word | What it means |
|---|---|
| Relationship | a link between two tables |
| One-to-many | one record in one table matches many in another |
| Many-to-many | many records in each table match many in the other |
| Linking table | a table that joins two tables with two foreign keys |
| Primary key | a field that is different for every record |
| Foreign key | a field that holds a primary key from another table |
| Form | a screen for adding, changing or deleting records |
The cells students fill in are left blank in this preview.
Starter: what you already know
Have a go
Starter: what you already know
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
A foreign key links two tables
A relationship is a link between two tables. In a one-to-many link, one record matches many in the other table. One member can have many sign-ups.
In a many-to-many link, each side has many matches. A member joins many activities and an activity has many members. Add a linking table in the middle. It holds two foreign keys, one to each table.
Try it: follow the links
-- signups holds two foreign keys. JOIN follows them to the other tables.
SELECT members.first_name, activities.activity_name
FROM signups
JOIN members ON signups.member_id = members.member_id
JOIN activities ON signups.activity_id = activities.activity_id
ORDER BY members.first_name, activities.activity_name;
-- Change the ORDER BY to put the activities in order first.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.