3.リレーション

学習目標

1 つのテーブルにデータを詰め込む問題を説明できる
主キーと外部キーの役割を説明できる
1 対多と多対多のリレーションを識別し、テーブル構造で表現できる
多対多を中間テーブルで表現する理由を理解する

1. テーブルの分割

bookstore のテーブルを見ると、書籍は books、著者は authors、カテゴリは categories と別々のテーブルに分かれています。これは、データの重複や更新時の不整合を避けるための設計です。

1-1. 単一テーブルの問題

書店の書籍と著者の情報を、1 つのテーブルで管理することを考えます。イメージは次の通りです。

+----+---------------------+-----------+---------+
| id | title               | author    | country |
+----+---------------------+-----------+---------+
|  1 | こころ              | 夏目漱石  | 日本    |
|  2 | 吾輩は猫である      | 夏目漱石  | 日本    |
|  3 | ノルウェイの森 上   | 村上春樹  | 日本    |
|  4 | ノルウェイの森 下   | 村上春樹  | 日本    |
|  5 | 海辺のカフカ 上     | 村上春樹  | 日本    |
+----+---------------------+-----------+---------+

この設計には次の問題があります。

データの重複: 同じ著者の情報（名前、出身国など）が、本の数だけ繰り返される。1 人の著者で 5 冊あれば 5 回、100 冊あれば 100 回書かれる
更新時の不整合: 「夏目漱石」の表記を変えたいとき、その著者の書籍すべての行を更新しないといけない。1 行でも更新漏れがあると、同じ著者が 2 つの表記で残ってしまう

1-2. 書籍と著者の分離

これらの問題を避けるため、bookstore は書籍と著者を別テーブルに分けています。

著者は authors テーブルに 1 人 1 行で登録されます。

USE bookstore;
SELECT id, name, country FROM authors WHERE name IN ('夏目漱石', '村上春樹');

+----+-----------+---------+
| id | name      | country |
+----+-----------+---------+
|  1 | 夏目漱石  | 日本    |
|  2 | 村上春樹  | 日本    |
+----+-----------+---------+

books テーブルには、著者の情報を直接書かず、代わりに著者を識別する author_id 列を置いています。

SELECT id, title, author_id FROM books LIMIT 5;

+----+----------------------+-----------+
| id | title                | author_id |
+----+----------------------+-----------+
|  1 | こころ               |         1 |
|  2 | 吾輩は猫である       |         1 |
|  3 | ノルウェイの森 上    |         2 |
|  4 | ノルウェイの森 下    |         2 |
|  5 | 海辺のカフカ 上      |         2 |
+----+----------------------+-----------+

author_id = 1 は、authors.id = 1 の行（夏目漱石）を指しています。著者の情報は authors に 1 行だけ存在するので、名前の訂正は 1 行の更新で済みます。

2. 主キー

主キー（primary key）は、テーブルの中で各行を区別するための目印となる列です。主キーの値は行ごとに重複しません。

2-1. 行の識別

authors テーブルには 38 人の著者が登録されています。主キーの id があるおかげで、「どの行がどの著者なのか」を間違いなく特定できます。

SELECT id, name FROM authors WHERE id = 2;

+----+-----------+
| id | name      |
+----+-----------+
|  2 | 村上春樹  |
+----+-----------+

id = 2 を指定すれば、その行は必ず 1 つだけ取れます。仮に同姓同名の著者がいたとしても、id が違うので別の行として扱えます。

主キー制約は CREATE TABLE で PRIMARY KEY と書くことで指定します。

CREATE TABLE authors (
  id   INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

2-2. 代理キーと自然キー

主キーに使う列の選び方には 2 つの考え方があります。

自然キー: 業務上の意味を持ち、一意に決まる値をそのまま主キーにする。例えば customers.email や books.isbn は、それぞれ値が一意になる
代理キー: 業務上の意味を持たない連番を新しく用意して主キーにする。authors.id や books.id がこれにあたる

実務では代理キー（連番の id）を使うのが一般的です。理由は次の通りです。

自然キーは後から変わる可能性がある（メールアドレスの変更など）。参照している他のテーブルも巻き込んで更新が必要になる
代理キーなら値が変わらず、参照する側も安定する

2-3. AUTO_INCREMENT

主キーの列に AUTO_INCREMENT を付けると、INSERT 時に値を指定しなくても MySQL が自動で連番を振ります。

CREATE TABLE authors (
  id   INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL
);

id 列を指定せずに行を追加しても、未使用の次の値が自動で入ります。

INSERT INTO authors (name) VALUES ('夏目漱石');

削除された値は原則再利用されません。

3. 外部キー

外部キー（foreign key）は、別のテーブルの主キーを参照する列です。books.author_id がこれにあたります。

3-1. 参照の宣言

books.author_id が authors.id を参照することを、CREATE TABLE で次のように明示します。

CREATE TABLE books (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  title     VARCHAR(100) NOT NULL,
  author_id INT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

FOREIGN KEY (author_id) REFERENCES authors(id) の行で、「author_id 列は authors テーブルの id 列を指す」という関係を宣言しています。

3-2. 参照整合性

外部キー制約があると、次のことが自動的に保証されます。

books.author_id に、authors.id として存在しない値は INSERT できない（例えば author_id = 999 で、authors に id = 999 がなければエラー）
authors から参照されている行は、先に books 側の参照を外さないと削除できない

この「関連先が必ず存在する」性質を 参照整合性 と呼びます。「存在しない著者の本」のような矛盾データを DB レベルで防げます。

4. リレーションの種類

テーブル同士の結びつきを リレーション と呼びます。2 つのテーブルのリレーションには、大きく分けて 3 種類があります。

4-1. 1 対多

片方のテーブルの 1 行が、もう一方の複数の行と関連する形です。最もよく現れるパターンです。

authors と books がその例です。

1 人の著者は複数の書籍を書ける
1 冊の書籍は 1 人の著者が書く

1 対多では、「多」の側に外部キー列を置きます。この例では books に author_id を置き、authors 側には何も追加しません。

村上春樹（authors.id = 2）は books に 5 冊の書籍があります。

+----+----------------------+-----------+
| id | title                | author_id |
+----+----------------------+-----------+
|  3 | ノルウェイの森 上    |         2 |
|  4 | ノルウェイの森 下    |         2 |
|  5 | 海辺のカフカ 上      |         2 |
|  6 | 海辺のカフカ 下      |         2 |
|  7 | 1Q84 BOOK1 前編      |         2 |
+----+----------------------+-----------+

各 books 行は 1 つの author_id を持ち、これで「どの本が誰の作品か」を表現できます。

bookstore にある他の 1 対多の例：

categories と books: 1 つのカテゴリに複数の書籍（books.category_id）
customers と sales: 1 人の顧客が複数回購入（sales.customer_id）

4-2. 多対多

両側が複数の行と関連する形です。

books と tags がその例です。

1 冊の書籍に複数のタグが付く
1 つのタグが複数の書籍に付く

この関係は、外部キー 1 列では表現できません。books.tag_id という列を作っても、1 冊には 1 タグしか付けられないからです。

多対多を表現するには、2 つのテーブルの間を取り持つ 中間テーブル を用意します。bookstore では book_tags がこれにあたります。

+---------+--------+
| book_id | tag_id |
+---------+--------+
|       3 |      1 |
|       3 |      2 |
+---------+--------+

book_tags は (book_id, tag_id) の組み合わせを記録するだけのテーブルです。書籍 ID 3 にタグを 2 つ付けるには、この表に 2 行入れます。

中間テーブルの定義は次の通りです。

CREATE TABLE book_tags (
  book_id INT NOT NULL,
  tag_id  INT NOT NULL,
  PRIMARY KEY (book_id, tag_id),
  FOREIGN KEY (book_id) REFERENCES books(id),
  FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

4-3. 1 対 1

両側の行が 1 対 1 で対応する関係です。実務では稀で、多くの場合 1 つのテーブルに統合できます。「ユーザーの基本情報」と「ユーザーの設定情報」のように、性質の異なる情報を意図的に分けたい場合に使います。本講義では扱いません。

5. 演習

演習: テーブル設計