Skip to content
Quantoros

What is a junction table?

SQLLevel: IntermediateDatabase Design

What is a junction table?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

A junction table (also called bridge table or associative table) implements a many-to-many relationship.

-- Many-to-many: students and courses
CREATE TABLE students (student_id SERIAL PRIMARY KEY, name VARCHAR(100));
CREATE TABLE courses  (course_id  SERIAL PRIMARY KEY, title VARCHAR(200));

-- Junction table
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id  INT REFERENCES courses(course_id),
    enrolled_at DATE,
    PRIMARY KEY (student_id, course_id)
);

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions