- Practice
- About 13 min
- 5 practice questions
Keys and relationships between tables
Distinguish primary, foreign and candidate keys and identify one-to-one, one-to-many and many-to-many relationships.
If something looks wrong, please tell us.
Real databases have several tables, linked to each other. The links are made with keys, and exam questions ask which key is which.
Keys
| Key | Meaning |
|---|---|
| Primary key | Chosen field that uniquely identifies each record; cannot be empty or repeated |
| Candidate key | Any field that could serve as a primary key |
| Foreign key | A field in one table that refers to the primary key of another table |
| Composite key | A primary key made of two or more fields together |
Relationships
- One-to-one: one record in A matches one record in B (a person and a passport).
- One-to-many: one record in A matches many in B (one class has many students).
- Many-to-many: many in A match many in B (students and subjects).
Worked examples
Example 1. A Class table has ClassID as the primary key. A Student table has a ClassID field too. What is ClassID in the Student table? A foreign key, because it points to the primary key of the Class table.
Example 2. One teacher teaches many students and each student has one class teacher. What is the relationship from teacher to students? One-to-many.
A foreign key points to another table's primary key. A primary key is unique and never empty.
Key takeaways: link tables with foreign keys, and identify the relationship by counting matches on each side.
Check your understanding
-
1. A Class table has ClassID as its primary key. The Student table also has a ClassID field. In the Student table, ClassID is a
- Composite key
- Foreign key
- Candidate key
- Sort key
Show answer
Answer: B. It points to the primary key of the Class table, so it is a foreign key.
-
2. Can a primary key column hold the same value in two records?
- Yes, if the records are in different rows
- Yes, but only twice
- No, every value must be unique
- Only when the value is empty
Show answer
Answer: C. A primary key is unique for every record and never empty.
-
3. One teacher teaches many students, and each student has one class teacher. From teacher to students, the relationship is
- One-to-one
- Many-to-many
- There is no relationship
- One-to-many
Show answer
Answer: D. One teacher record matches many student records: one-to-many.
-
4. Each person has one passport and each passport belongs to one person. The relationship is
- One-to-one
- One-to-many
- Many-to-many
- Many-to-one
Show answer
Answer: A. One record on each side matches exactly one on the other: one-to-one.
-
5. A primary key made of two fields together, such as (RollNo, SubjectCode), is a
- Foreign key
- Composite key
- Sort key
- Data dictionary
Show answer
Answer: B. A key formed from two or more fields together is a composite key.
Sign in to keep track of the lessons you finish.