Be able to create relationships between tables

📚 18 Databases – Creating Relationships Between Tables

What is a Relationship?

A relationship is a way to link data in one table to data in another. Think of it like a family tree – each person (row) is connected to their parents and children (other rows in different tables). In databases, we use keys to make those connections.

Key Types

• Primary Key (PK) – unique identifier for each row. Like a student’s unique ID.
• Foreign Key (FK) – a field that links to a PK in another table. Like a student’s class ID that points to the Classes table.

Example Scenario

We’ll model a simple school system with two tables: Students and Classes.

Step‑by‑Step: Adding a Relationship

1. Define the Classes table first so that ClassID exists.
2. Create the Students table and add ClassID as a column.
3. Set ClassID in Students as a Foreign Key that references Classes.ClassID.
4. When inserting a student, ensure the ClassID value matches an existing class.
5. Use queries like:

   SELECT s.FirstName, s.LastName, c.ClassName
   FROM Students s
   JOIN Classes c ON s.ClassID = c.ClassID;
       

Visual Analogy

Imagine a school bus (Classes) that carries many students. Each student has a bus ticket (ClassID) that tells the bus which route they’re on. The bus ticket is the foreign key linking the student to the bus.

Quick Quiz

• What is the purpose of a Primary Key?
• Which column in the Students table is the Foreign Key?
• Write a simple SQL statement to find all students in the “Biology 101” class.