Relationships Between Tables in MySQL

Relational databases are called “relational” because tables are connected through relationships.

In MySQL, relationships are created using primary keys and foreign keys.

Understanding relationships helps you:

  • Design proper databases
  • Avoid data duplication
  • Maintain consistency
  • Write better JOIN queries

1. What Is a Relationship?

A relationship defines how data in one table is connected to data in another table.

Example:

  • One department has many students
  • One customer places many orders
  • One employee has one ID card

These connections are called relationships.

2. One-to-One Relationship (1:1)

In a one-to-one relationship:

One record in Table A is related to only one record in Table B.

Example

Each employee has one ID card.

Employees

emp_id

name

1

A

2

B

ID_Cards

card_id

emp_id

101

1

102

2

Each employee has exactly one card.

How to Implement in MySQL

CREATE TABLE Employees (

    emp_id INT PRIMARY KEY,

    name VARCHAR(50)

);

 

CREATE TABLE ID_Cards (

    card_id INT PRIMARY KEY,

    emp_id INT UNIQUE,

    FOREIGN KEY (emp_id) REFERENCES Employees(emp_id)

);

Important:
The UNIQUE constraint ensures one-to-one mapping.

3. One-to-Many Relationship (1:N)

This is the most common relationship.

One record in Table A can be related to many records in Table B.

Example

One department has many students.

Departments

dept_id

dept_name

101

Computer

102

Electrical

Students

student_id

name

dept_id

1

Rahul

101

2

Anita

101

3

Aman

102

One department → Many students
But one student → Only one department

How to Implement

CREATE TABLE Departments (

    dept_id INT PRIMARY KEY,

    dept_name VARCHAR(50)

);

 

CREATE TABLE Students (

    student_id INT PRIMARY KEY,

    name VARCHAR(50),

    dept_id INT,

    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)

);

Here:

  • dept_id in Students is a foreign key.
  • It connects each student to a department.

4. Many-to-Many Relationship (M: N)

In this relationship:

Many records in Table A relate to many records in Table B.

This cannot be implemented directly.
It requires a third table called a junction table.

Example

Students and Courses

  • One student can enroll in many courses
  • One course can have many students

Students

student_id

name

Courses

| course_id | course_name |

Enrollment (Junction Table)

| student_id | course_id |

Implementation

CREATE TABLE Enrollment (

    student_id INT,

    course_id INT,

    PRIMARY KEY (student_id, course_id),

    FOREIGN KEY (student_id) REFERENCES Students(student_id),

    FOREIGN KEY (course_id) REFERENCES Courses(course_id)

);

Here:

  • Composite primary key prevents duplicates.
  • Two foreign keys create relationships with both tables.

This is how many-to-many relationships are implemented.

5. Implementing Relationships in MySQL

Relationships are created using:

  • PRIMARY KEY (in parent table)
  • FOREIGN KEY (in child table)

Basic structure:

FOREIGN KEY (column_name)

REFERENCES parent_table(parent_column);

The parent table must have:

  • PRIMARY KEY or UNIQUE constraint

The child table stores:

  • The foreign key

6. Referential Integrity

Referential integrity ensures that relationships remain valid.

It means:

  • A foreign key value must exist in the parent table.
  • You cannot insert invalid references.

Example:

If dept_id 999 does not exist in Departments:

INSERT INTO Students VALUES (4, 'Riya', 999);

This will fail.

Referential integrity prevents broken relationships.

7. Cascading Actions

When a parent row changes or is deleted, MySQL allows automatic actions using cascading rules.

ON DELETE CASCADE

If the parent row is deleted → related child rows are deleted automatically.

FOREIGN KEY (dept_id)

REFERENCES Departments(dept_id)

ON DELETE CASCADE;

If a department is deleted → all related students are deleted.

ON UPDATE CASCADE

If the primary key changes → foreign key updates automatically.

ON UPDATE CASCADE

Other Options

  • SET NULL → Sets foreign key to NULL
  • RESTRICT → Prevents deletion if related rows exist
  • NO ACTION → Similar to RESTRICT

8. Why Relationships Are Important

Relationships:

  • Reduce data redundancy
  • Maintain consistency
  • Enforce referential integrity
  • Enable powerful JOIN queries
  • Improve database structure

Without relationships:

  • Data becomes inconsistent
  • Duplicate information increases
  • Queries become unreliable

Summary

There are three main types of relationships:

  1. One-to-One
  2. One-to-Many
  3. Many-to-Many

They are implemented using:

  • Primary Keys
  • Foreign Keys
  • Junction Tables (for many-to-many)

Referential integrity ensures valid relationships.
Cascading actions control what happens when parent data changes.