Introduction to Relational Table Relationships in MySQL

Course: MySQL Training

Topic: Introduction to Relational Table Relationships

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Relational Table Relationships

Relational table relationships are a fundamental concept in MySQL and relational database management systems. They describe how records in one table are connected to records in another table through related columns. These relationships help organize information, reduce unnecessary duplication, maintain data consistency, and retrieve related information efficiently.

In a real-world application, information is rarely stored in one large table. An online shopping application might have separate tables for customers, orders, products, and payments. A customer can place multiple orders, and each order can contain multiple products. Relationships connect these tables so that the database can represent the complete business process.

For example, a customer table may contain customer details, while an orders table stores order information. Instead of repeating the customer's name and address in every order record, the orders table can store the customer's ID. MySQL can use that ID to connect an order to the correct customer.

Relational database relationships are commonly implemented using primary keys and foreign keys. A primary key uniquely identifies a record, while a foreign key references a key in another table. MySQL supports foreign key constraints to help maintain consistency between related tables. Read the official MySQL foreign key documentation.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand relational tables and how they represent connected data.
• Explain primary keys and foreign keys.
• Understand one-to-one, one-to-many, and many-to-many relationships.
• Identify parent tables and child tables.
• Create related tables using MySQL.
• Define foreign key constraints.
• Understand referential integrity and its importance.
• Use JOIN statements to retrieve data from related tables.
• Understand how relationships affect INSERT, UPDATE, and DELETE operations.
• Apply table relationships in practical database projects.

3. What Is a Relational Table?

A relational table stores information in rows and columns. Each row represents a record, and each column represents an attribute of that record.

For example, a customers table may contain a customer ID, customer name, email address, and city.

Each customer is represented by one row. The customer_id column identifies each customer and can be used by other tables to refer to that customer.

3.1 Why Use Multiple Related Tables?

• Reduce duplication: Store customer information once rather than repeating it in every order.
• Improve consistency: Update a customer's details in one location.
• Maintain data integrity: Use foreign keys to prevent invalid references.
• Support complex queries: Combine related data using JOIN operations.
• Improve organization: Separate different business entities into logical tables.
• Support scalability: Maintain a structured design as an application grows.

4. Understanding Primary Keys

A primary key is a column or combination of columns that uniquely identifies each row in a table. A primary key must contain unique values and cannot contain NULL values.

4.1 Example of a Primary Key

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(150),
    city VARCHAR(50)
);

In this table, customer_id is the primary key. Every customer must have a unique ID, allowing other tables to reference the correct customer.

4.2 Characteristics of a Primary Key

• Each primary key value must be unique.
• A primary key cannot contain NULL values.
• A table can have only one primary key constraint, although the key can contain multiple columns.
• Primary keys are commonly used to identify records in related tables.
• Primary key values should be stable and not changed unnecessarily.

4.3 Using AUTO_INCREMENT

MySQL supports AUTO_INCREMENT for automatically generating numeric identifiers.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

When a new product is inserted without specifying product_id, MySQL automatically generates an identifier.

5. Understanding Foreign Keys

A foreign key is a column or group of columns in one table that references a primary key or another eligible unique key in a related table. It establishes a connection between records and can enforce referential integrity.

For example, the customer_id column in an orders table can reference customer_id in the customers table. This allows each order to be associated with an existing customer.

5.1 Example of a Foreign Key

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE,
    total_amount DECIMAL(10,2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The foreign key constraint ensures that an order cannot reference a customer ID that does not exist in the referenced customers table, provided normal foreign key checks are enabled.

5.2 Primary Key vs Foreign Key

6. What Is a Table Relationship?

A table relationship describes how records in one table correspond to records in another table. Relationships are designed according to the rules of the business or application.

For example, in a school database, a student can enroll in multiple courses, and a course can contain multiple students. In an e-commerce database, one customer can place several orders, and an order can contain multiple products.

The three commonly discussed relationship types are:

1. One-to-one relationship.
2. One-to-many relationship.
3. Many-to-many relationship.

These relationship types describe the logical connection between records. Primary keys, foreign keys, unique constraints, and junction tables are commonly used to implement them.

7. One-to-One Relationship

A one-to-one relationship occurs when one record in a table corresponds to at most one record in another table, and vice versa. This relationship is useful when information is separated into different tables for security, organization, or optional data storage.

7.1 Real-World Example

Consider a users table and a user_profiles table. Each user can have one profile, and each profile belongs to one user.

7.2 Create the Users Table

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE
);

7.3 Create the User Profiles Table

CREATE TABLE user_profiles (
    profile_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    phone VARCHAR(20),
    address VARCHAR(255),
    date_of_birth DATE,
    CONSTRAINT fk_profile_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

The UNIQUE constraint on user_profiles.user_id prevents multiple profile rows from referencing the same user. The foreign key ensures that each profile references an existing user.

7.4 Insert Sample Data

INSERT INTO users (username, email)
VALUES
('amit', '[email protected]'),
('priya', '[email protected]');

INSERT INTO user_profiles
(user_id, phone, address, date_of_birth)
VALUES
(1, '9876543210', 'Mumbai', '2000-05-15'),
(2, '9876543211', 'Pune', '1999-08-20');

7.5 Retrieve Related Records

SELECT
    u.user_id,
    u.username,
    u.email,
    p.phone,
    p.address
FROM users AS u
JOIN user_profiles AS p
    ON u.user_id = p.user_id;

The query joins the users and user_profiles tables using their shared user ID.

7.6 Common Uses of One-to-One Relationships

• User accounts and additional profile information.
• Employees and optional confidential employee details.
• Products and separate product specifications.
• Customers and additional verification records.

8. One-to-Many Relationship

A one-to-many relationship occurs when one record in a parent table can be associated with multiple records in a child table. Each child record normally references one parent record.

This is one of the most common relationships in relational databases.

8.1 Real-World Example

One customer can place many orders, but each order belongs to one customer. The customers table is the parent table, and the orders table is the child table.

8.2 Create the Customers Table

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    city VARCHAR(50)
);

8.3 Create the Orders Table

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

8.4 Insert Sample Customers and Orders

INSERT INTO customers (customer_name, email, city)
VALUES
('Amit Sharma', '[email protected]', 'Mumbai'),
('Priya Verma', '[email protected]', 'Pune'),
('Rahul Mehta', '[email protected]', 'Delhi');

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
(1, '2026-01-10', 2500.00),
(1, '2026-02-15', 1800.00),
(2, '2026-02-20', 3200.00),
(3, '2026-03-05', 1500.00);

8.5 Display Customers and Their Orders

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers AS c
JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date;

The result contains one row per matching customer-order combination. Amit appears twice because he has placed two orders.

8.6 Find Customers with No Orders

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This query identifies customers who do not currently have a matching order. It uses a LEFT JOIN to retain all customers and filters for rows without a matching order.

8.7 Common Uses of One-to-Many Relationships

• Customers and orders.
• Departments and employees.
• Categories and products.
• Authors and books, when each book has one primary author.
• Blogs and comments.
• Invoices and invoice line items.

9. Many-to-Many Relationship

A many-to-many relationship occurs when multiple records in one table can relate to multiple records in another table.

For example, a student can enroll in many courses, and each course can have many students. A direct single foreign key in either table cannot represent all these associations cleanly. A junction table, also called a bridge or associative table, is commonly used to implement this relationship.

9.1 Relationship Structure

The relationship can be represented as:

students
   |
   | one-to-many
   v
student_courses
   ^
   | many-to-one
   |
courses

The junction table contains a foreign key referencing students and another foreign key referencing courses. Each row represents one student's enrollment in one course.

9.2 Create the Students Table

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE
);

9.3 Create the Courses Table

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    course_fee DECIMAL(10,2)
);

9.4 Create the Junction Table

CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_sc_student
        FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    CONSTRAINT fk_sc_course
        FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

The composite primary key (student_id, course_id) prevents the same student-course combination from being entered more than once. If the business allows a student to enroll in the same course multiple times, the design would need an additional enrollment identifier or another key strategy.

9.5 Insert Sample Data

INSERT INTO students (student_name, email)
VALUES
('Amit Sharma', '[email protected]'),
('Priya Verma', '[email protected]'),
('Rahul Mehta', '[email protected]');

INSERT INTO courses (course_name, course_fee)
VALUES
('MySQL Training', 8000.00),
('Python Programming', 10000.00),
('Data Analytics', 12000.00);

INSERT INTO student_courses
(student_id, course_id, enrollment_date)
VALUES
(1, 1, '2026-01-10'),
(1, 2, '2026-01-15'),
(2, 1, '2026-02-01'),
(2, 3, '2026-02-10'),
(3, 2, '2026-03-01');

9.6 Display Students and Their Courses

SELECT
    s.student_name,
    c.course_name,
    sc.enrollment_date
FROM student_courses AS sc
JOIN students AS s
    ON sc.student_id = s.student_id
JOIN courses AS c
    ON sc.course_id = c.course_id
ORDER BY s.student_name, c.course_name;

This query joins three tables to display each student's course enrollments. A student can appear multiple times if they are enrolled in several courses, and a course can appear for multiple students.

9.7 Common Uses of Many-to-Many Relationships

• Students and courses.
• Products and orders.
• Movies and actors.
• Users and roles.
• Articles and tags.
• Doctors and patients, when both sides can have multiple associations.

10. Comparing the Three Relationship Types

11. Parent Tables and Child Tables

A parent table contains the referenced key values, while a child table contains foreign key values that refer to the parent. These terms describe the direction of the foreign key relationship.

11.1 Parent Table Example

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

11.2 Child Table Example

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

Here, departments is the parent table and employees is the child table. The child table's department_id references the department's primary key.

11.3 Why This Matters

• A department must exist before an employee can reference it through this foreign key.
• A parent record cannot normally be deleted while referenced child records remain if the constraint uses the default restrictive behavior.
• Referential actions can define how related rows are handled when parent keys are updated or deleted.
• The relationship makes it easier to retrieve employee and department information together.

12. Referential Integrity

Referential integrity means that references between related tables remain valid. A foreign key constraint helps enforce this rule by preventing child records from referring to nonexistent parent records.

For example, if an order references customer_id = 5, the referenced customer must exist in the customers table when the foreign key is checked.

12.1 Example of a Valid Reference

INSERT INTO customers
(customer_name, email, city)
VALUES
('Neha Kapoor', '[email protected]', 'Mumbai');

INSERT INTO orders
(customer_id, order_date, total_amount)
VALUES
(4, '2026-04-01', 2200.00);

This example assumes the newly inserted customer receives ID 4 in the current sample database. In practice, check the generated ID rather than assuming it will always be 4.

12.2 Example of an Invalid Reference

INSERT INTO orders
(customer_id, order_date, total_amount)
VALUES
(9999, '2026-04-02', 1500.00);

If customer 9999 does not exist, MySQL rejects the insert when the foreign key is enabled and enforced. This protects the database from storing an order associated with a nonexistent customer.

12.3 Benefits of Referential Integrity

• Prevents orphan records in child tables.
• Helps preserve consistent relationships across tables.
• Reduces errors caused by invalid identifiers.
• Makes database rules explicit and enforceable.
• Supports reliable reporting and data retrieval.

13. Foreign Key Actions: ON DELETE and ON UPDATE

MySQL allows foreign key definitions to specify what happens when a referenced parent row is deleted or its key is updated. The choice should reflect the business rules and the importance of preserving historical data.

13.1 RESTRICT

RESTRICT prevents a parent row from being deleted or updated when doing so would violate the foreign key relationship. This is useful when child records must remain valid and should not be automatically removed.

CREATE TABLE orders_restrict (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    CONSTRAINT fk_order_restrict_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
);

13.2 CASCADE

CASCADE propagates a parent key update or deletion to matching child rows. For example, ON DELETE CASCADE deletes dependent child rows when the parent row is deleted.

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_name VARCHAR(100),
    quantity INT NOT NULL,
    CONSTRAINT fk_item_order
        FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Use cascading deletes carefully. Deleting a parent record can remove multiple dependent records, potentially including information needed for audits or historical reporting.

13.3 SET NULL

SET NULL sets the child foreign key to NULL when the referenced parent row is deleted or updated, where supported and valid. The child column must allow NULL values.

CREATE TABLE support_tickets (
    ticket_id INT PRIMARY KEY,
    assigned_employee_id INT NULL,
    description VARCHAR(255),
    CONSTRAINT fk_ticket_employee
        FOREIGN KEY (assigned_employee_id)
        REFERENCES employees(employee_id)
        ON DELETE SET NULL
);

This design allows a support ticket to remain even if its assigned employee is deleted, while removing the reference to that employee.

13.4 NO ACTION

In MySQL, NO ACTION is treated as RESTRICT for InnoDB foreign keys. It prevents the operation from violating the constraint.

13.5 Comparison of Foreign Key Actions

14. Using JOINs to Retrieve Related Data

Relationships define how tables are connected. JOIN operations use matching values to retrieve information from those tables in one query.

14.1 INNER JOIN

An INNER JOIN returns rows that have matching values in both tables.

SELECT
    c.customer_name,
    o.order_id,
    o.total_amount
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id;

Customers without matching orders are not included in this result.

14.2 LEFT JOIN

A LEFT JOIN returns all rows from the left table and matching rows from the right table. When no match exists, the right-table columns contain NULL.

SELECT
    c.customer_name,
    o.order_id,
    o.total_amount
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id;

This query includes customers who have not placed orders.

14.3 Join Three Related Tables

SELECT
    o.order_id,
    c.customer_name,
    oi.product_name,
    oi.quantity
FROM orders AS o
JOIN customers AS c
    ON o.customer_id = c.customer_id
JOIN order_items AS oi
    ON o.order_id = oi.order_id;

This example assumes the order_items table has been created and contains records. It demonstrates how one query can combine customer, order, and order-item information.

14.4 Why JOINs Matter

• Display customer details alongside orders.
• Retrieve employee names alongside department names.
• Display students and their enrolled courses.
• Generate reports across multiple related tables.
• Summarize related data using aggregate functions.

15. Normalization and Table Relationships

Normalization is the process of organizing database tables to reduce unnecessary duplication and improve data integrity. Table relationships play an important role in normalized database design.

15.1 Example of a Poorly Designed Table

A poorly designed orders table might repeat customer details for every order.

Customer information is repeated across multiple records. If the customer's email changes, multiple rows may need to be updated.

15.2 A Better Relational Design

Store customer details in a customers table and order details in an orders table. Connect them using customer_id.

customers
---------
customer_id
customer_name
customer_email

orders
------
order_id
customer_id
order_date
total_amount

This structure reduces repetition and allows customer information to be managed separately from order information.

15.3 Benefits of Normalization

• Reduces redundant data.
• Helps prevent update anomalies.
• Improves consistency across records.
• Creates a clearer logical data model.
• Makes relationships easier to maintain.

16. Composite Keys and Relationships

A composite key is a key made up of two or more columns. It can be used when a single column does not uniquely identify a record.

16.1 Example of a Composite Primary Key

CREATE TABLE course_enrollments (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

The combination of student ID and course ID uniquely identifies an enrollment. A student can enroll in different courses, but the same student-course combination cannot appear twice under this design.

16.2 When Composite Keys Are Useful

• Student-course enrollment tables.
• Product-category mapping tables.
• User-role assignment tables.
• Other junction tables where a combination of identifiers defines a unique association.

17. Self-Referencing Relationships

A self-referencing relationship occurs when a table references itself. This is useful when records have a hierarchical or parent-child structure within the same entity type.

17.1 Example: Employees and Managers

An employee may report to another employee who acts as their manager. Both employees and managers are stored in the same table.

CREATE TABLE staff (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INT NULL,
    CONSTRAINT fk_staff_manager
        FOREIGN KEY (manager_id)
        REFERENCES staff(employee_id)
);

The manager_id column references employee_id in the same table. A NULL manager ID can represent an employee who has no manager recorded, such as the head of an organization.

17.2 Insert Sample Data

INSERT INTO staff (employee_name, manager_id)
VALUES
('Neha Director', NULL),
('Amit Manager', 1),
('Priya Developer', 2),
('Rahul Analyst', 2);

This example assumes the generated employee IDs are 1, 2, 3, and 4 in a new table. In a database containing previous records, retrieve the actual IDs instead of relying on these values.

17.3 Display Employees and Their Managers

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM staff AS e
LEFT JOIN staff AS m
    ON e.manager_id = m.employee_id;

The query uses the staff table twice with different aliases. This is a common pattern for displaying hierarchical relationships.

18. Adding a Foreign Key to an Existing Table

A foreign key can be added after table creation using the ALTER TABLE statement. Before adding the constraint, ensure existing child values match valid parent keys and the columns have compatible data types.

18.1 Add a Foreign Key

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

If the table already has a foreign key with the same name, choose a different constraint name. If the relationship already exists, you do not need to add it again.

18.2 Remove a Foreign Key Constraint

ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer;

Removing the foreign key constraint removes the database rule that enforces that relationship. It does not automatically delete the child column.

18.3 View a Table's Relationship Definition

SHOW CREATE TABLE orders;

This statement displays the table definition, including its primary key, indexes, and foreign key constraints.

19. Common Errors When Creating Table Relationships

19.1 Referencing a Nonexistent Parent Key

Problem: The child table contains a foreign key that references a parent record that does not exist.

Solution: Insert the parent record first, use a valid referenced key, and check that foreign key enforcement is enabled.

19.2 Mismatched Column Data Types

Problem: The foreign key column and referenced key column have incompatible definitions.

Solution: Use compatible column types. For fixed-precision numeric types, the size and signedness must match; character columns also need compatible character sets and collations where applicable.

19.3 Deleting a Parent Record with Child Records

Problem: MySQL rejects a delete because child records still reference the parent.

Solution: Decide whether the business rule requires retaining the parent, deleting dependent records, or setting the foreign key to NULL. Configure the appropriate referential action carefully.

19.4 Duplicate Values in a One-to-One Relationship

Problem: Multiple child rows reference the same parent even though the intended relationship is one-to-one.

Solution: Add a UNIQUE constraint to the child foreign key column.

19.5 Forgetting a Junction Table

Problem: A many-to-many relationship is stored by placing multiple IDs in a single column or repeatedly overwriting a single foreign key.

Solution: Create a junction table containing separate foreign keys for each related entity.

19.6 Existing Invalid Data

Problem: Adding a foreign key fails because existing child records contain values without matching parent records.

Solution: Find and correct or remove invalid records according to the business rules before adding the constraint.

20. Best Practices for Designing Relational Table Relationships

• Give each main entity a stable primary key.
• Use foreign keys to represent genuine relationships between tables.
• Choose one-to-one, one-to-many, or many-to-many structures based on the real business rules.
• Use a junction table for many-to-many relationships.
• Use UNIQUE constraints when a foreign key must not repeat.
• Keep referenced and referencing column types compatible.
• Choose ON DELETE and ON UPDATE actions deliberately.
• Avoid cascading deletes when they could remove important historical information.
• Use meaningful table names, column names, and constraint names.
• Normalize tables to reduce unnecessary duplication.
• Use JOINs to retrieve related data rather than repeatedly storing the same information.
• Test inserts, updates, and deletes to verify that relationships behave as expected.
• Review indexes and query plans when related-table queries become slow.
• Document important business rules, especially for optional relationships and historical records.

21. Practical Project: Online Shopping Database

Project objective: Design a simple relational database for an online shopping system. The database should store customers, products, orders, and order items using appropriate relationships.

21.1 Create the Customers Table

CREATE TABLE shop_customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE
);

21.2 Create the Products Table

CREATE TABLE shop_products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

21.3 Create the Orders Table

CREATE TABLE shop_orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    CONSTRAINT fk_shop_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES shop_customers(customer_id)
);

21.4 Create the Order Items Table

CREATE TABLE shop_order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_shop_item_order
        FOREIGN KEY (order_id)
        REFERENCES shop_orders(order_id),
    CONSTRAINT fk_shop_item_product
        FOREIGN KEY (product_id)
        REFERENCES shop_products(product_id)
);

The order_items table is a junction-style table that connects orders and products. It also stores relationship-specific information such as quantity and the unit price used for the order.

21.5 Insert Sample Data

INSERT INTO shop_customers (customer_name, email)
VALUES
('Amit Sharma', '[email protected]'),
('Priya Verma', '[email protected]');

INSERT INTO shop_products (product_name, price)
VALUES
('Laptop', 50000.00),
('Mouse', 500.00),
('Keyboard', 1500.00);

INSERT INTO shop_orders (customer_id, order_date)
VALUES
(1, '2026-04-01'),
(1, '2026-04-05'),
(2, '2026-04-06');

INSERT INTO shop_order_items
(order_id, product_id, quantity, unit_price)
VALUES
(1, 1, 1, 50000.00),
(1, 2, 2, 500.00),
(2, 3, 1, 1500.00),
(3, 2, 1, 500.00);

These insert statements assume the tables are new and the generated IDs match the listed values. In a production workflow, obtain the generated identifiers rather than assuming specific IDs.

21.6 Display Orders with Customer Names

SELECT
    o.order_id,
    c.customer_name,
    o.order_date
FROM shop_orders AS o
JOIN shop_customers AS c
    ON o.customer_id = c.customer_id
ORDER BY o.order_id;

21.7 Display Products in Each Order

SELECT
    o.order_id,
    c.customer_name,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM shop_orders AS o
JOIN shop_customers AS c
    ON o.customer_id = c.customer_id
JOIN shop_order_items AS oi
    ON o.order_id = oi.order_id
JOIN shop_products AS p
    ON oi.product_id = p.product_id
ORDER BY o.order_id, p.product_name;

21.8 Calculate the Total Value of Each Order

SELECT
    o.order_id,
    c.customer_name,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM shop_orders AS o
JOIN shop_customers AS c
    ON o.customer_id = c.customer_id
JOIN shop_order_items AS oi
    ON o.order_id = oi.order_id
GROUP BY o.order_id, c.customer_name
ORDER BY o.order_id;

21.9 Project Learning Outcomes

• Created multiple related tables.
• Defined primary and foreign keys.
• Implemented one-to-many relationships.
• Used a junction table to connect orders and products.
• Retrieved related records with JOIN statements.
• Calculated order totals from related table data.

22. Practice Exercises

Complete the following exercises to test your understanding of relational table relationships.

1. Create a departments table with a primary key.
2. Create an employees table that references departments.
3. Insert two departments and at least four employees.
4. Write a query to display employee names and department names.
5. Create a one-to-one relationship between users and user profiles.
6. Explain why a UNIQUE constraint is required on the profile's user ID for a one-to-one design.
7. Create students and courses tables with a student_courses junction table.
8. Write a query to list all students and their course names.
9. Find customers who have not placed any orders using a LEFT JOIN.
10. Explain the difference between RESTRICT, CASCADE, and SET NULL.
11. Use SHOW CREATE TABLE to inspect a foreign key constraint.
12. Describe a real-world example of a self-referencing relationship.

23. Practice Exercise Answers

Answer 1: Create a Departments Table

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Answer 2: Create an Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

Answer 4: Display Employee and Department Names

SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
JOIN departments AS d
    ON e.department_id = d.department_id;

Answer 6: Why Use UNIQUE in a One-to-One Relationship?

The UNIQUE constraint prevents multiple profile records from referencing the same user. Without it, several profiles could reference one user, making the relationship one-to-many instead of one-to-one.

Answer 8: List Students and Their Course Names

SELECT
    s.student_name,
    c.course_name
FROM student_courses AS sc
JOIN students AS s
    ON sc.student_id = s.student_id
JOIN courses AS c
    ON sc.course_id = c.course_id;

Answer 9: Customers Without Orders

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Answer 11: Inspect a Foreign Key

SHOW CREATE TABLE orders;

24. MySQL Relational Table Relationships Interview Questions and Answers

Q1. What is a relational table relationship?

Answer: It is a logical connection between records in different tables, usually implemented using primary keys and foreign keys.

Q2. What is a primary key?

Answer: A primary key uniquely identifies each record in a table and cannot contain NULL values.

Q3. What is a foreign key?

Answer: A foreign key references a key in another table, or sometimes in the same table, and can enforce referential integrity.

Q4. What is a one-to-one relationship?

Answer: It is a relationship where each record corresponds to at most one record on the other side. A foreign key with a UNIQUE constraint is a common implementation.

Q5. What is a one-to-many relationship?

Answer: It allows one parent record to be related to multiple child records. For example, one customer can have multiple orders.

Q6. What is a many-to-many relationship?

Answer: It allows multiple records on each side to relate to multiple records on the other side. A junction table typically implements this relationship.

Q7. What is a junction table?

Answer: A junction table stores associations between two tables, usually using foreign keys to both. It can also store attributes of the association.

Q8. What is referential integrity?

Answer: Referential integrity helps ensure that foreign key values reference valid records in the related table.

Q9. What does ON DELETE CASCADE do?

Answer: It automatically deletes matching child rows when a referenced parent row is deleted.

Q10. What does ON DELETE SET NULL do?

Answer: It sets the child foreign key to NULL when the parent row is deleted, provided the column allows NULL and the action is valid for the relationship.

Q11. What is the difference between INNER JOIN and LEFT JOIN?

Answer: INNER JOIN returns matching rows from both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table, using NULL for unmatched right-side columns.

Q12. Why is normalization important?

Answer: Normalization helps reduce unnecessary data duplication, improve consistency, and organize information into related tables.

Q13. What is a composite key?

Answer: A composite key consists of two or more columns used together to uniquely identify a record.

Q14. What is a self-referencing relationship?

Answer: It occurs when a table contains a foreign key referencing its own key, such as an employee's manager ID referencing another employee.

Q15. How can you view a table's foreign key definition?

Answer: Use SHOW CREATE TABLE table_name; to inspect the table definition, including foreign key constraints.

25. Quick Revision Checklist

• A relational table stores records in rows and attributes in columns.
• A primary key uniquely identifies a row.
• A foreign key references a key in a related table.
• A one-to-one relationship commonly uses a UNIQUE foreign key.
• A one-to-many relationship normally stores the foreign key in the child table.
• A many-to-many relationship commonly uses a junction table.
• Referential integrity helps prevent invalid references.
• ON DELETE and ON UPDATE define how related records respond to parent changes.
• JOIN statements retrieve data from related tables.
• Normalization reduces unnecessary duplication.
• Composite keys combine multiple columns into one key.
• Self-referencing relationships support hierarchical structures.
• SHOW CREATE TABLE helps inspect table definitions and constraints.

26. Conclusion

Relational table relationships are essential for building organized, reliable, and scalable MySQL databases. They allow information to be separated into logical tables while preserving the connections between customers and orders, students and courses, employees and departments, and many other related entities.

Understanding primary keys, foreign keys, one-to-one relationships, one-to-many relationships, many-to-many relationships, junction tables, and referential integrity helps you design effective databases. Combining these concepts with JOIN statements allows you to retrieve and analyse information across multiple tables without unnecessary duplication.

Continue Learning: Explore the MySQL Training Course to learn more about relational databases, SQL queries, and database design.

Course Demo: Register for a Course Demo to learn more about the training program.

Official MySQL Reference: MySQL Foreign Key Constraints Documentation