PostgreSQL many-to-many Relationship

In this example we will look at how to work with a many-to-many relationship.  So first let’s look at a situation where we can leverage a many-to-many relationship.  Imagine we have both Students and Courses.  A student can be enrolled in multiple courses and a course can have multiple students.  This is called a many-to-many relationship and requires what’s called a pivot table.  Although there aren’t any restrictions to the name of a pivot table I will call it “course_student”.  The course_student pivot table will have, at minimum, 2 foreign key fields.  A reference to courses table and a reference to students table.  

 CREATE TABLE students (

   id INT PRIMARY KEY AUTO_INCREMENT,

   name VARCHAR(100) NOT NULL,

   phone_number VARCHAR(100) UNIQUE NOT NULL,

 );

 CREATE TABLE courses (

   id INT PRIMARY KEY AUTO_INCREMENT,

   title VARCHAR(100) NOT NULL,

   description TEXT NOT NULL

 );

 -- The pivot table has a reference key to each table

 CREATE TABLE course_student (

   student_id INT,

   course_id INT,

   PRIMARY KEY (student_id, course_id),

   FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,

   FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

Below are a few useful ways to query this schema. Assuming you are familiar with a LEFT JOIN, these queries specifically use LEFT JOIN for connecting the three tables. courses to course_student to students

# Get all courses that student with id = 2 is enrolled

SELECT c.id course_id, c.title, s.id student_id, s.name

FROM students s

LEFT JOIN course_student cs ON s.id = cs.student_id

LEFT JOIN course c ON c.id = cs.course_id

WHERE s.id = 2;

# Get all students enrolled in course with id = 1

 SELECT c.id course_id, c.title, s.id student_id, s.name

 FROM courses c

 LEFT JOIN course_student cs ON c.id = cs.course_id

 LEFT JOIN student s ON s.id = cs.student_id

 WHERE c.id = 1;