1対1と1対多
外部キーはどちらに置くか
1対多の関係が決まったら、次は外部キーの列をどちらのテーブルに置くかを決めます。答えは1つしかありません。
1対多の外部キーは、必ず多の側に置く。
店舗と注文なら、orders に store_id を置きます。stores に order_id を置くことはできません。
なぜ1の側に置けないのか
置いてみると分かります。渋谷店には注文が3件あります。stores に order_id を1列だけ置いたら、3件のうちどれを入れればいいのでしょうか。
id | name | order_id
1 | 渋谷店 | 1 <- 2 と 3 はどこへorder_id_1、order_id_2 と列を増やす手は、4件目が来た瞬間に破綻します。渋谷店の行を3行に増やす手は、店名が3回書かれることになり、第1章で見た更新不整合そのものです。
列は増やせないが、行は増やせる。 だから多の側に置きます。注文が何件増えても orders に行が増えるだけで、列の数は変わりません。
1対1はどちらでもよい、ように見える
注文と配送伝票が1対1だとします。orders に shipment_id を置いても、shipments に order_id を置いても、形の上では成立します。
判断の軸はどちらが後から生まれるか、そして0を許すかです。注文は成立するが配送はまだ、という時間帯があります。もし orders に shipment_id を置くと、注文した瞬間はそこが NULL になります。逆に shipments に order_id を置けば、配送が決まったときに1行足すだけで済み、NULL が出ません。
NULL が出ない側に置く、と覚えておけばだいたい間違えません。
1対1に必要なもう1つの仕掛け
shipments に order_id を置いただけでは、同じ注文に配送伝票を2枚作れてしまいます。それでは1対多です。
1対1にするには UNIQUE が要ります。
order_id integer NOT NULL UNIQUE REFERENCES orders (id)外部キーだけでは1対多、外部キーに UNIQUE を足すと1対1になります。この1行の違いが多重度を決めています。
手を動かす
orders に外部キーを足して、店舗と顧客への線を引きます。名前も自分で付けます。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL,
customer_id integer NOT NULL
);
INSERT INTO stores VALUES
(1, '渋谷店', '東京都'),
(2, '梅田店', '大阪府'),
(3, '博多店', '福岡県');
INSERT INTO customers VALUES
(1, '田中あかり'),
(2, '佐藤けんじ'),
(3, '鈴木みか');
INSERT INTO orders VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 3);
期待される出力
| id | name | order_count |
|---|---|---|
| 1 | 渋谷店 | 2 |
| 2 | 梅田店 | 1 |
| 3 | 博多店 | 0 |