How is a many-to-many relationship implemented in a relational database?

In a relational database, a many-to-many relationship is implemented through an intermediate (linking) table. This table contains foreign keys to both related tables.

For example, there are tables students and courses. One student can be enrolled in multiple courses, and one course can include multiple students. To implement this relationship, a student_courses table is created with two columns: student_id and course_id, each referencing the respective table.

Example structure:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

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

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)
);

Thus, a separate record is created in the linking table for each student-course pair.