• 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

Types of keys
KeyMeaning
Primary keyChosen field that uniquely identifies each record; cannot be empty or repeated
Candidate keyAny field that could serve as a primary key
Foreign keyA field in one table that refers to the primary key of another table
Composite keyA 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.

— Rule to remember

Key takeaways: link tables with foreign keys, and identify the relationship by counting matches on each side.

Check your understanding

  1. 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

    1. Composite key
    2. Foreign key
    3. Candidate key
    4. Sort key
    Show answer

    Answer: B. It points to the primary key of the Class table, so it is a foreign key.

  2. 2. Can a primary key column hold the same value in two records?

    1. Yes, if the records are in different rows
    2. Yes, but only twice
    3. No, every value must be unique
    4. Only when the value is empty
    Show answer

    Answer: C. A primary key is unique for every record and never empty.

  3. 3. One teacher teaches many students, and each student has one class teacher. From teacher to students, the relationship is

    1. One-to-one
    2. Many-to-many
    3. There is no relationship
    4. One-to-many
    Show answer

    Answer: D. One teacher record matches many student records: one-to-many.

  4. 4. Each person has one passport and each passport belongs to one person. The relationship is

    1. One-to-one
    2. One-to-many
    3. Many-to-many
    4. Many-to-one
    Show answer

    Answer: A. One record on each side matches exactly one on the other: one-to-one.

  5. 5. A primary key made of two fields together, such as (RollNo, SubjectCode), is a

    1. Foreign key
    2. Composite key
    3. Sort key
    4. Data dictionary
    Show answer

    Answer: B. A key formed from two or more fields together is a composite key.

Privacy · Terms · Refunds · Delivery