女性向けエンジニアスクール
3秒でわかる
多対多の関係を 2 つの一対多に分解するための表。学生と講義のように双方が複数を持つ関係を、外部キー 2 本を並べた行で記録するときに使います。
もう少し詳しく
どういうものか
中間テーブルは、2 つのテーブルの多対多の関係を保持するためだけに置く表である。関連する 2 つのテーブルの主キーを外部キーとして 2 列持ち、その組み合わせ 1 つを 1 行で表す。中間表、連関エンティティ、junction table、join table などとも呼ぶ。
学生 1 人が複数の講義を取り、講義 1 つに複数の学生が登録するという関係なら、enrollments という中間テーブルに student_id と course_id を並べる。
なぜ必要か
リレーショナルデータベースは多対多を直接表現できない。students に course_id を 1 列持たせると 1 人 1 講義しか登録できないし、"3,7,12" のようにカンマ区切りで詰め込むと、その講義の受講者を数える検索が一気に難しくなり、外部キー制約も効かなくなる。
中間テーブルを挟むと、多対多は「学生 対 中間」と「講義 対 中間」の 2 つの一対多に分解され、どちらも普通の JOIN で扱えるようになる。
具体例
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
enrolled_at DATE NOT NULL,
grade CHAR(1),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
-- 「データベース論」の受講者名を取り出す
SELECT s.name
FROM students AS s
JOIN enrollments AS e ON e.student_id = s.id
JOIN courses AS c ON c.id = e.course_id
WHERE c.title = 'データベース論';主キーを 2 列の複合キーにしておくと、同じ学生が同じ講義に二重登録されるのをデータベース側で防げる。
つまずきやすいところ
中間テーブルを「ただのつなぎ」と考えて、関係そのものが持つ属性を置き忘れることが多い。上の例の enrolled_at や grade は学生の属性でも講義の属性でもなく、受講という関係の属性なので、中間テーブルが唯一の置き場所になる。
もう 1 つ、複合主キーを付けずに id だけの単独主キーにしてしまうと、同じ組み合わせの行がいくつでも入る。単独主キーを使う設計を選ぶ場合でも、2 列に UNIQUE 制約は付けておく。
似た用語との違い
外部キーは「どのテーブルのどの行を指すか」という 1 本の参照そのものを指し、中間テーブルはその参照を 2 本まとめて持つ表を指す。ORM の世界では、中間テーブルにモデルを作らず裏に隠す機能 (Django の ManyToManyField など) もあるが、実体としては同じ表が作られている。
