One-to-Many (1:N) Relationships0%

Many-to-Many (M:N) Relationships & Junction Tables

One-to-Many (1:N) Relationships

Intermediate12 min read•Updated: 2026-09-12

One-to-Many (1:N) Relationships: The Backbone of Relational Modeling

The One-to-Many (1:N) relationship is the single most ubiquitous structural pattern in relational database design. A department employs many workers, a customer places many orders, a blog author writes many posts, and a class has many students. In every case, a single record in the Parent Table correlates to zero, one, or hundreds of records in the Child Table.

1. The Cardinal Rule of 1:N Relationships

[!CRITICAL] Where does the Foreign Key go? The Foreign Key ALWAYS resides on the "Many" (Child) side of the relationship, pointing back to the Primary Key of the "One" (Parent) side!

 ONE SIDE (Parent): departments
 +---------+--------------------+
 | dept_id | dept_name          |  <-- dept_id is PRIMARY KEY
 +---------+--------------------+
 |       1 | Engineering        |
 |       2 | Sales              |
 +---------+--------------------+
            ^
            | 1 : N Relationship
            |
 MANY SIDE (Child): employees (FK: dept_id)
 +--------+----------------+---------+
 | emp_id | emp_name       | dept_id |  <-- Foreign Key points to parent!
 +--------+----------------+---------+
 |    101 | Aarav Sharma   |       1 |  <-- Belongs to Engineering
 |    102 | Diya Patel     |       1 |  <-- Belongs to Engineering
 |    103 | Rohan Verma    |       1 |  <-- Belongs to Engineering
 |    104 | Kabir Mehta    |       2 |  <-- Belongs to Sales
 +--------+----------------+---------+

2. DDL Implementation with Referential Actions

Let us build a complete e-commerce relationship: Customers (1) -> Orders (N):

1-- Parent Table: Customers (The "One" side)
2CREATE TABLE customers (
3    customer_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    customer_name VARCHAR(100) NOT NULL,
5    email VARCHAR(100) NOT NULL UNIQUE
6) ENGINE = InnoDB;
7 
8-- Child Table: Orders (The "Many" side)
9CREATE TABLE orders (
10    order_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
11    customer_id INT UNSIGNED NOT NULL, -- The Foreign Key
12    order_date DATE NOT NULL,
13    total_amount DECIMAL(10, 2) NOT NULL,
14    order_status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled') NOT NULL DEFAULT 'Pending',
15    
16    -- Establishing Foreign Key Constraint:
17    CONSTRAINT fk_orders_customer
18        FOREIGN KEY (customer_id) 
19        REFERENCES customers(customer_id)
20        ON DELETE RESTRICT -- Do not allow deleting a customer who has orders!
21        ON UPDATE CASCADE  -- If customer_id changes, update orders automatically!
22) ENGINE = InnoDB;

3. Querying 1:N Relationships

Joining Parent and Child:

1SELECT 
2    c.customer_name,
3    c.email,
4    o.order_id,
5    o.order_date,
6    o.total_amount
7FROM customers c
8INNER JOIN orders o ON c.customer_id = o.customer_id;

Aggregating across the 1:N Boundary:

1-- How many orders has each customer placed, and what is their lifetime spend?
2SELECT 
3    c.customer_id,
4    c.customer_name,
5    COUNT(o.order_id) AS total_orders_placed,
6    IFNULL(SUM(o.total_amount), 0.00) AS total_lifetime_spent
7FROM customers c
8LEFT JOIN orders o ON c.customer_id = o.customer_id
9GROUP BY c.customer_id, c.customer_name
10ORDER BY total_lifetime_spent DESC;

4. Identifying Orphaned Child Rows

In poorly maintained legacy databases where foreign keys were omitted, child tables frequently contain orphaned records (child rows pointing to non-existent parent IDs). You can detect them using a LEFT JOIN:

1-- Find orders whose customer has vanished from the customers table:
2SELECT o.order_id, o.customer_id
3FROM orders o
4LEFT JOIN customers c ON o.customer_id = c.customer_id
5WHERE c.customer_id IS NULL;

5. Best Practices & Common Pitfalls

Always Index the Foreign Key: While MySQL automatically indexes foreign keys in child tables, ensure composite indexes align with your query filters (e.g., INDEX idx_cust_date (customer_id, order_date)).
Careful with RESTRICT vs CASCADE: While ON DELETE CASCADE is convenient, in accounting and billing, deleting a customer must never delete historical financial transactions. Always use ON DELETE RESTRICT for financial records.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In a One-to-Many (1:N) relationship between authors and books, which table must contain the Foreign Key column?

Question 2

What referential action prevents an administrator from deleting a customer if that customer has existing order records?

Question 3

What is an "orphaned" row in a child table?

Question 4

Which query pattern detects orphaned records in a child table orders that has no matching customers parent?

Question 5

Why should aggregate queries joining a 1:N relationship (e.g. Customers to Orders) use LEFT JOIN instead of INNER JOIN when generating customer reports?

Next Lesson

Many-to-Many (M:N) Relationships & Junction Tables

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
One-to-One (1:1) Relationships	Many-to-Many (M:N) Relationships & Junction Tables

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Many-to-Many (M:N) Relationships & Junction Tables

One-to-Many (1:N) Relationships

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

One-to-Many (1:N) Relationships: The Backbone of Relational Modeling

1. The Cardinal Rule of 1:N Relationships

[!CRITICAL] Where does the Foreign Key go? The Foreign Key ALWAYS resides on the "Many" (Child) side of the relationship, pointing back to the Primary Key of the "One" (Parent) side!

 ONE SIDE (Parent): departments
 +---------+--------------------+
 | dept_id | dept_name          |  <-- dept_id is PRIMARY KEY
 +---------+--------------------+
 |       1 | Engineering        |
 |       2 | Sales              |
 +---------+--------------------+
            ^
            | 1 : N Relationship
            |
 MANY SIDE (Child): employees (FK: dept_id)
 +--------+----------------+---------+
 | emp_id | emp_name       | dept_id |  <-- Foreign Key points to parent!
 +--------+----------------+---------+
 |    101 | Aarav Sharma   |       1 |  <-- Belongs to Engineering
 |    102 | Diya Patel     |       1 |  <-- Belongs to Engineering
 |    103 | Rohan Verma    |       1 |  <-- Belongs to Engineering
 |    104 | Kabir Mehta    |       2 |  <-- Belongs to Sales
 +--------+----------------+---------+

2. DDL Implementation with Referential Actions

Let us build a complete e-commerce relationship: Customers (1) -> Orders (N):

1-- Parent Table: Customers (The "One" side)
2CREATE TABLE customers (
3    customer_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    customer_name VARCHAR(100) NOT NULL,
5    email VARCHAR(100) NOT NULL UNIQUE
6) ENGINE = InnoDB;
7 
8-- Child Table: Orders (The "Many" side)
9CREATE TABLE orders (
10    order_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
11    customer_id INT UNSIGNED NOT NULL, -- The Foreign Key
12    order_date DATE NOT NULL,
13    total_amount DECIMAL(10, 2) NOT NULL,
14    order_status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled') NOT NULL DEFAULT 'Pending',
15    
16    -- Establishing Foreign Key Constraint:
17    CONSTRAINT fk_orders_customer
18        FOREIGN KEY (customer_id) 
19        REFERENCES customers(customer_id)
20        ON DELETE RESTRICT -- Do not allow deleting a customer who has orders!
21        ON UPDATE CASCADE  -- If customer_id changes, update orders automatically!
22) ENGINE = InnoDB;

3. Querying 1:N Relationships

Joining Parent and Child:

1SELECT 
2    c.customer_name,
3    c.email,
4    o.order_id,
5    o.order_date,
6    o.total_amount
7FROM customers c
8INNER JOIN orders o ON c.customer_id = o.customer_id;

Aggregating across the 1:N Boundary:

1-- How many orders has each customer placed, and what is their lifetime spend?
2SELECT 
3    c.customer_id,
4    c.customer_name,
5    COUNT(o.order_id) AS total_orders_placed,
6    IFNULL(SUM(o.total_amount), 0.00) AS total_lifetime_spent
7FROM customers c
8LEFT JOIN orders o ON c.customer_id = o.customer_id
9GROUP BY c.customer_id, c.customer_name
10ORDER BY total_lifetime_spent DESC;

4. Identifying Orphaned Child Rows

1-- Find orders whose customer has vanished from the customers table:
2SELECT o.order_id, o.customer_id
3FROM orders o
4LEFT JOIN customers c ON o.customer_id = c.customer_id
5WHERE c.customer_id IS NULL;

5. Best Practices & Common Pitfalls

Always Index the Foreign Key: While MySQL automatically indexes foreign keys in child tables, ensure composite indexes align with your query filters (e.g., INDEX idx_cust_date (customer_id, order_date)).
Careful with RESTRICT vs CASCADE: While ON DELETE CASCADE is convenient, in accounting and billing, deleting a customer must never delete historical financial transactions. Always use ON DELETE RESTRICT for financial records.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In a One-to-Many (1:N) relationship between authors and books, which table must contain the Foreign Key column?

Question 2

What referential action prevents an administrator from deleting a customer if that customer has existing order records?

Question 3

What is an "orphaned" row in a child table?

Question 4

Which query pattern detects orphaned records in a child table orders that has no matching customers parent?

Question 5

Why should aggregate queries joining a 1:N relationship (e.g. Customers to Orders) use LEFT JOIN instead of INNER JOIN when generating customer reports?

Next Lesson

Many-to-Many (M:N) Relationships & Junction Tables

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
One-to-One (1:1) Relationships	Many-to-Many (M:N) Relationships & Junction Tables

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next