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 が要ります。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
loan_id integer NOT NULL UNIQUE REFERENCES loans (id)外部キーだけでは1対多、外部キーに UNIQUE を足すと、親 1 件に子は多くても 1 件 (1 対 0 または 1) になります。この1行の違いが多重度を決めています。
既存の表に関係を足す
既に列がある場合は、表を作り直さず外部キーだけを追加します。
SQL クエリ
ALTER TABLE borrowings ADD CONSTRAINT borrowings_library_fk
FOREIGN KEY (library_id) REFERENCES libraries (id);既存の library_id がすべて参照先にあるときに追加できます。演習では2本の参照を補い、用意済みの確認 SQL で件数と制約の本数を見ます。
結果の1列に別の件数を添えるには、1行1列を返す SELECT を括弧に入れます。これをスカラー副問い合わせと呼びます。
SQL クエリ
SELECT name, (SELECT count(*) FROM borrowings) AS all_loans
FROM libraries
ORDER BY id;count(*) の結果は1行なので、各図書館の行に同じ全体件数を添えられます。演習ではこの部分も用意済みです。
手を動かす
orders に外部キーを足して、店舗と顧客への線を引きます。名前も自分で付けます。
orders には store_id と customer_id がありますが、外部キーがまだ付いていません。ALTER TABLE で2本足してください。制約名も指定します。
orders_store_fk—store_idがstores (id)を参照するorders_customer_fk—customer_idがcustomers (id)を参照する
足したら、店舗ごとの注文件数を id、name、order_count と、orders に付いた外部キーの本数 fk_count の4列で、id の順に取り出してください。注文が0件の店舗も含めます。
件数と制約を確認する SQL は用意済みです。2本の外部キーを追加します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 2 行を補います。確認用の SQL は用意してあります。
テーブル構造
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 | fk_count |
|---|---|---|---|
| 1 | 渋谷店 | 2 | 2 |
| 2 | 梅田店 | 1 | 2 |
| 3 | 博多店 | 0 | 2 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い