One-to-many relationship design in SQL - Step-by-Step Execution

Concept Flow - One-to-many relationship design

Create Parent Table

↓

Create Child Table with Foreign Key

↓

Insert Parent Rows

↓

Insert Child Rows linked to Parent

↓

Query Join Parent and Child

↓

Result: Parent with multiple Children

This flow shows how to create two tables where one parent row can have many child rows linked by a foreign key, then how to insert and query them.

Execution Sample

SQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

This code creates two tables: authors (parent) and books (child) with a foreign key linking books to authors.

Execution Table

Step	Action	SQL Statement	Effect	Result
1	Create authors table	CREATE TABLE authors (author_id INT PRIMARY KEY, name VARCHAR(50));	Table created	Empty authors table
2	Create books table with foreign key	CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100), author_id INT, FOREIGN KEY (author_id) REFERENCES authors(author_id));	Table created with FK constraint	Empty books table
3	Insert author 1	INSERT INTO authors VALUES (1, 'Alice');	Row inserted	authors: 1 row (Alice)
4	Insert author 2	INSERT INTO authors VALUES (2, 'Bob');	Row inserted	authors: 2 rows (Alice, Bob)
5	Insert book 1 linked to author 1	INSERT INTO books VALUES (101, 'Book A', 1);	Row inserted	books: 1 row (Book A by Alice)
6	Insert book 2 linked to author 1	INSERT INTO books VALUES (102, 'Book B', 1);	Row inserted	books: 2 rows (Book A, Book B by Alice)
7	Insert book 3 linked to author 2	INSERT INTO books VALUES (103, 'Book C', 2);	Row inserted	books: 3 rows (Book A, Book B by Alice; Book C by Bob)
8	Query join authors and books	SELECT a.name, b.title FROM authors a JOIN books b ON a.author_id = b.author_id;	Rows returned	Result: Alice | Book A Alice | Book B Bob | Book C
9	Exit	No more steps	End of demonstration	Shows one-to-many relationship

💡 All steps executed to show creation, insertion, and querying of one-to-many relationship

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	Final
authors table rows	empty	(1, 'Alice')	(1, 'Alice'), (2, 'Bob')	(1, 'Alice'), (2, 'Bob')	(1, 'Alice'), (2, 'Bob')	(1, 'Alice'), (2, 'Bob')	(1, 'Alice'), (2, 'Bob')
books table rows	empty	empty	empty	(101, 'Book A', 1)	(101, 'Book A', 1), (102, 'Book B', 1)	(101, 'Book A', 1), (102, 'Book B', 1), (103, 'Book C', 2)	(101, 'Book A', 1), (102, 'Book B', 1), (103, 'Book C', 2)

Key Moments - 3 Insights

Why do we need a foreign key in the child table?

The foreign key links each child row to a parent row, ensuring the child belongs to an existing parent. See execution_table step 2 where the foreign key is created.

Can a parent have zero children?

Yes, a parent can exist without any child rows. The foreign key is in the child table, so no child rows means no linked rows. This is why authors can exist without books.

What happens if we try to insert a child with a non-existing parent ID?

The database will reject the insert due to foreign key constraint violation. This prevents orphan child rows. This is implied by the foreign key in step 2.

Visual Quiz - 3 Questions

Look at the execution_table at step 6, how many books belong to author 1?

A 1

B 3

C 2

D 0

💡 Hint

Check the books table rows after step 6 in variable_tracker; two books have author_id 1.

At which step does the foreign key constraint get established?

A Step 2

B Step 1

C Step 3

D Step 5

💡 Hint

See execution_table step 2 where books table is created with FOREIGN KEY.

If we tried to insert a book with author_id 3 (not existing), what would happen?

A Insert succeeds with no issues

B Insert fails due to foreign key constraint

C Author 3 is automatically created

D Book is inserted but author_id is set to NULL

💡 Hint

Foreign key constraints prevent child rows linking to non-existing parents, as explained in key_moments.

Concept Snapshot

One-to-many relationship design:
- Parent table has primary key
- Child table has foreign key referencing parent
- One parent row can link to many child rows
- Foreign key enforces data integrity
- Query with JOIN to combine related rows

Full Transcript

This visual execution shows how to design a one-to-many relationship in SQL. First, we create a parent table 'authors' with a primary key. Then, we create a child table 'books' with a foreign key referencing the parent's primary key. We insert authors and books linked by author_id. The foreign key ensures each book belongs to an existing author. Finally, we query both tables with a JOIN to see authors and their books. This design allows one author to have many books, demonstrating the one-to-many relationship clearly.

Practice

1. What does a one-to-many relationship in a database mean?

easy

A. One record in a table relates to many records in another table

B. Many records in a table relate to one record in the same table

C. One record relates to exactly one record in another table

D. Many records relate to many records in another table

Solution

1. Step 1: Understand relationship types

   A one-to-many relationship means one record in a table connects to multiple records in another table.

2. Step 2: Match definition to options

   One record in a table relates to many records in another table correctly describes this as one record relating to many records in another table.

3. Final Answer:

   One record in a table relates to many records in another table -> Option A

4. Quick Check:

   One-to-many = one record to many records [OK]

Hint: One-to-many means one record links to many records [OK]

Common Mistakes:

• Confusing one-to-many with many-to-many
• Thinking one-to-many means one record links to one record
• Mixing up the direction of the relationship

2. Which SQL statement correctly creates a foreign key for a one-to-many relationship from Orders to Customers?

easy

A. ALTER TABLE Customers ADD FOREIGN KEY (OrderID) REFERENCES Orders(OrderID);

B. ALTER TABLE Customers ADD FOREIGN KEY (CustomerID) REFERENCES Orders(OrderID);

C. ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

D. ALTER TABLE Orders ADD FOREIGN KEY (OrderID) REFERENCES Customers(CustomerID);

Solution

1. Step 1: Identify the 'many' and 'one' tables

   Orders is the 'many' side, Customers is the 'one' side in a one-to-many relationship.

2. Step 2: Add foreign key in 'many' table referencing 'one' table

   The foreign key should be in Orders referencing Customers, so ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); is correct.

3. Final Answer:

   ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); -> Option C

4. Quick Check:

   Foreign key in 'many' table references 'one' table [OK]

Hint: Foreign key goes in 'many' table pointing to 'one' table [OK]

Common Mistakes:

• Placing foreign key in the 'one' table instead of 'many'
• Referencing wrong columns between tables
• Mixing table names in foreign key definition

3. Given these tables:
Customers(CustomerID, Name)
Orders(OrderID, CustomerID, Amount)
What will this query return?

SELECT Customers.Name, COUNT(Orders.OrderID) AS OrderCount FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.Name;

medium

A. List of customers who have placed at least one order

B. List of customers with total amount spent on orders

C. List of orders with customer names repeated for each order

D. List of customers with the number of orders each placed, including customers with zero orders

Solution

1. Step 1: Understand the LEFT JOIN usage

   LEFT JOIN keeps all customers, even those without matching orders.

2. Step 2: COUNT(Orders.OrderID) counts orders per customer

   Grouping by customer name counts how many orders each customer has, zero if none.

3. Final Answer:

   List of customers with the number of orders each placed, including customers with zero orders -> Option D

4. Quick Check:

   LEFT JOIN + COUNT = all customers with order counts [OK]

Hint: LEFT JOIN + COUNT counts all, including zero matches [OK]

Common Mistakes:

• Thinking COUNT counts total amount spent
• Assuming only customers with orders appear
• Confusing JOIN types and their effects

4. You wrote this SQL to create a one-to-many relationship:

CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

But you get an error. What is the most likely cause?

medium

A. OrderID should not be primary key in Orders

B. Customers table does not exist or CustomerID is not a primary key

C. Foreign key should be in Customers table, not Orders

D. CustomerID column type must be VARCHAR, not INT

Solution

1. Step 1: Check foreign key reference validity

   Foreign key must reference an existing table and a primary or unique key column.

2. Step 2: Verify Customers table and CustomerID key

   If Customers table or CustomerID primary key is missing, error occurs.

3. Final Answer:

   Customers table does not exist or CustomerID is not a primary key -> Option B

4. Quick Check:

   Foreign key references must exist and be keys [OK]

Hint: Foreign key target must exist and be primary/unique key [OK]

Common Mistakes:

• Assuming foreign key can reference non-key columns
• Placing foreign key in wrong table
• Mismatching data types between foreign key and referenced key

5. You have two tables:
Authors(AuthorID, Name)
Books(BookID, Title, AuthorID)
You want to find authors who have written more than 3 books. Which query is correct?

hard

A. SELECT Name FROM Authors JOIN Books ON Authors.AuthorID = Books.AuthorID GROUP BY Name HAVING COUNT(BookID) > 3;

B. SELECT Name FROM Authors LEFT JOIN Books ON Authors.AuthorID = Books.AuthorID WHERE COUNT(BookID) > 3;

C. SELECT Name FROM Books GROUP BY AuthorID HAVING COUNT(BookID) > 3;

D. SELECT Name FROM Authors WHERE AuthorID IN (SELECT AuthorID FROM Books WHERE COUNT(BookID) > 3);

Solution

1. Step 1: Join Authors and Books on AuthorID

   We join to connect authors with their books.

2. Step 2: Group by author name and filter by book count

   Use GROUP BY Name and HAVING COUNT(BookID) > 3 to find authors with more than 3 books.

3. Final Answer:

   SELECT Name FROM Authors JOIN Books ON Authors.AuthorID = Books.AuthorID GROUP BY Name HAVING COUNT(BookID) > 3; -> Option A

4. Quick Check:

   JOIN + GROUP BY + HAVING filters authors by book count [OK]

Hint: Use GROUP BY and HAVING to filter by count in one-to-many [OK]

Common Mistakes:

• Using WHERE with aggregate functions instead of HAVING
• Missing GROUP BY clause
• Using LEFT JOIN but filtering with WHERE on aggregate