Suggest an edit

Improve this article

Refine the answer for “Tell me about a many:many relationship”. Your changes go to moderation before they’re published.

Approval required

Content

What you’re changing

Title (EN)

Short answer (EN)

Shown above the full answer for quick recall.

Answer (EN)

A **many-to-many (M:N)** relationship is when **each record in one table can relate to several records in another**,
and vice versa, **each record in the second table** can relate to several in the first.

This kind of relationship **can't be implemented directly** - it requires a **junction (linking) table**.

### Example: students and courses

One student can enroll in several courses,
and one course can be taken by several students.

#### Students table:

```sql
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);
```

#### Courses table:

```sql
CREATE TABLE courses (
  id INT PRIMARY KEY,
  title VARCHAR(100)
);
```

#### Junction table:

```sql
CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);
```

The `student_courses` table stores pairs of "which student, which course."
This creates a **two-way relationship**:
many students <-> many courses.

### How it looks logically

| students | courses | student_courses |
|---|---|---|
| 1, Anna | 10, SQL | (1, 10) |
| 2, Ivan | 11, Python | (1, 11) |
|  |  | (2, 10) |

Anna takes **SQL and Python**,
and the **SQL** course is taken by **Anna and Ivan**.

### Example query

To get all students and the courses they're enrolled in:

```sql
SELECT 
  students.name, 
  courses.title
FROM student_courses
JOIN students ON student_courses.student_id = students.id
JOIN courses ON student_courses.course_id = courses.id;
```

### Where M:N relationships are used

- Students <-> Courses
- Authors <-> Books
- Users <-> Roles
- Doctors <-> Patients
- Products <-> Categories

### Details

- It's implemented **via a junction table** holding two foreign keys.
- That table's **primary key is usually composite** (`(id1, id2)`).
- The junction table often stores extra data too, such as an enrollment date.

**Summary:**
A **many-to-many** relationship connects objects that can relate **both ways to many elements**.
It's implemented via **a third table** that links the two main ones.

Markdown · drag & drop images · ⌘B / ⌘I shortcuts325 words

For the reviewer

Note to the moderator (optional)

Visible only to the moderator. Helps review go faster.