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.