Last updated Nov 17, 2024

In relational databases, a one-to-many relationship is the most fundamental type of relationship. This relationship exists when a record in one table can be associated with multiple records in another table.

For example, consider a database of a bookstore. Here, we have two tables: Authors and Books. An author can write multiple books, but each book is written by only one author. This is a classic one-to-many relationship.

It’s important to understand exactly why this is a one-to-many relationship. Why can we can have many books per author but only one author per book? The structure of the table tells us this is how the relationship must work. Here’s how:

• Single Author, Multiple Books: Each author in the Authors table can write multiple books. An author’s ID can appear in many rows in the Books table. For instance, if “George Orwell” writes two books, his ID appears next to each of those books in the Books table. Therefore an author can have many books.

• Single Book, Single Author: Conversely, each book in the Books table is associated with only one author. For each row in the Books table, there is only a place for one AuthorID. For example, “1984” can only be associated with one author, George Orwell.

• A primary key is a unique identifier assigned to each record in a table. It ensures that each record can be distinctly identified, meaning no two records can have the same primary key value. This key is essential for maintaining the uniqueness and integrity of the data within the table.
• In the Authors table, AuthorID serves as the primary key. Each author has a unique AuthorID that distinguishes it from others.
• In the Books table, the BookID serves as the primary key. Each book has a unique BookID that distinguishes it from others.

• A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. Put simply, a foreign key is a reference to the primary key of another table. It establishes a link between tables, showing how records in one table relate to records in another.
• In the Books table, AuthorID serves as a foreign key. It references the AuthorID from the Authors table.
• This foreign key creates the link between each book and its author, establishing the one-to-many relationship.

• SQL - W3 Table JOINs - Introduction
• SQL - W3 Table JOINs - DROP Tables
• SQL - W3 Magic Store Database Overview
• SQL - W3 Table JOINs - One-to-Many Relationships
• Next: SQL - W3 Table JOINs - JOIN Tables with One-to-Many Relationships
• SQL - W3 Table JOINs - Filter and Sort with JOINs
• SQL - W3 One-to-Many JOINs - Practice Assignment