テーブル間の ONE-TO-MANY リレーションのモデリング

さて、そろそろスキルをレベルアップして、リレーショナルデータベースで超よく出てくる ONE-TO-MANY リレーションを作ってみよう。

小さな会社を想像してみて。1人の社員は1つの部署にしか所属できないけど、1つの部署には何十人もの社員がいることもあるよね。現実世界の2つのオブジェクト、社員と部署がある。この関係は「1つの部署にたくさんの社員がいる」って感じで説明できるし、フォーマルに言うと「1対多」(ONE-TO-MANY)だよ。

同じように ONE-TO-MANY の関係はどこにでもある：

• 1人のクライアントがたくさんの注文をすることができる；
• 1人の著者がたくさんの本を書くことができる；
• 1人の先生が複数の生徒に教えることができる。

リレーショナルデータベースで ONE-TO-MANY のリレーションは外部キー（FOREIGN KEY）を使って実現するよ。「多」(MANY)側のテーブルのカラムが「1」(ONE)側のテーブルの主キーを参照する感じ。

ONE-TO-MANY リレーションの作り方

じゃあ、クラシックな例でやってみよう：顧客と注文のリレーション。1人の顧客はたくさんの注文をするけど、1つの注文は1人の顧客にしか紐付かない。2つのテーブルを作るよ：customers（顧客）と orders（注文）。

customers テーブル

これが「1」側のテーブル。顧客の情報を保存するよ。

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- 顧客のユニークID
    name TEXT NOT NULL              -- 顧客の名前
);

orders テーブル

こっちは「多」側のテーブル。注文を保存してて、各注文には customer_id という外部キーがあって、customers テーブルの customer_id を参照してる。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- 注文のユニークID
    order_date DATE NOT NULL,              -- 注文日
    customer_id INT REFERENCES customers(customer_id) -- 外部キー
);

実践編

データの挿入

じゃあ、テーブルにちょっとデータを入れてリレーションがちゃんと動くか見てみよう。

customers テーブルに顧客を追加する：

INSERT INTO customers (name)
VALUES
    ('Ada Lovelace'),
    ('Grace Hopper'),
    ('Linus Torvalds');

結果：

orders テーブルに注文を追加する：

INSERT INTO orders (order_date, customer_id)
VALUES
    ('2023-10-01', 1),  -- Adaの注文
    ('2023-10-02', 2),  -- Graceの注文
    ('2023-10-03', 1);  -- Adaのもう一つの注文

orders テーブル：

注意：注文を追加するときは必ず存在する customer_id を指定しよう。存在しないIDを指定すると、DBがエラーを投げるよ。これがデータの整合性を守る仕組み！

リレーションの確認

じゃあ、テーブルがどう繋がってるか見てみよう。例えば「Ada Lovelaceがした注文は？」って聞いてみる：

SELECT orders.order_id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.name = 'Ada Lovelace';

結果：

ここでは JOIN コマンドを使って、外部キーで2つのテーブルをくっつけてる。便利だし、見やすいし、データの重複もなし！

なんで必要なの？

ONE-TO-MANY のリレーションは現実世界でめっちゃよく使うし、超便利。例えばネットショップだと、何千人もの顧客と何百万件もの注文があるよね。もし注文ごとに顧客情報を全部書いてたら、データが膨大になるし、管理も大変。顧客情報は1つのテーブルにまとめて、注文は別のテーブルにすることで、データ量も減るし、DBもスッキリ！

さらに、データを繋げることで強力な分析クエリも書ける。「各顧客が何件注文した？」とか「先月注文した顧客は誰？」みたいな質問も簡単にできるよ。

ハマりがちなポイント

初心者がよくやっちゃうミスはこれ：

外部キーを忘れる。 「多」側のテーブルで外部キーを指定し忘れると、リレーションは頭の中だけで、DBはそれを守ってくれない。つまり、存在しない顧客に紐付いた注文ができちゃって、DBが壊れるリスクがあるよ。

「1」側のレコードを消そうとする。 例えば customers から顧客を消すと、orders の注文が「宙ぶらりん」になる。これを防ぐには ON DELETE CASCADE を使って、顧客を消したらその注文も自動で消えるようにできる。

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE
);

これで顧客を消すと：

DELETE FROM customers WHERE customer_id = 1;

その人の注文も全部消える。DBがコーヒーみたいにスッキリきれいになるよ。

データ挿入時のミス。 存在しない customer_id で注文を入れようとすると、こんなエラーが出る：

ERROR:  insert or update on table "orders" violates foreign key constraint