28 min read
Foreign Keys
Loans store MemberID and BookID, not names and titles. A foreign key is another table's primary key, stored here.
Store IDs, Not Names
Emily Brooks is at the desk with Bristol Harbour. George Clarke writes: Sophie Walsh / The River Path — then crosses it out. It is Emily, not Sophie, and there are two Sophie Walshs.
Amelia Hart already has MemberID and BookID. Emily is MemberID 3. Bristol Harbour is BookID 2.
George starts Loans. Row 104: LoanID 104, MemberID 3, BookID 2. No name. No title.
A loan should store MemberID and BookID. Those numbers already identify a member row and a book row.
The field on Loans that stores Members' MemberID is a foreign key. The field that stores Books' BookID is another foreign key. Store the other table's identity, not a second copy of the name.
Click the table name, a column heading, or any value. Each piece has a job.
| Members — the rows those numbers point at | |||
|---|---|---|---|
| Books | ||
|---|---|---|
A Pointer, Not a Second Copy
Members.MemberID is stored again on Loans
The bar marks the ONE side (the primary key). The three lines mark the MANY side (the foreign key). The line joins those two fields — it is not a flow of data.
A foreign key is a pointer, not a second copy of the person. Follow MemberID 1 into Members for Sophie's city.
Storing FullName on the loan would mean two places to update. The two Sophie Walshes would collide again. Store MemberID. Look up the name when you need it.
Practice
Swipe or use the arrows to move between questions.
1 / 3
More lessons in Database Fundamentals · Next: How Keys Connect Tables · Previous: Choosing Primary Keys