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