多対多と中間テーブル
多対多はそのままでは作れない
注文と商品の関係を両方向から聞いてみます。
- 1件の注文に、商品はいくつ含まれるか → 1つ以上
- 1つの商品は、いくつの注文に含まれるか → 0件以上
両方が「多」なので多対多です。ところが、この関係はそのままではテーブルに置けません。
前回のとおり、外部キーは多の側に置きます。しかし両方が多なので、置き場所が決まりません。orders に product_id を置けば1注文1商品になってしまうし、products に order_id を置けば1商品1注文になってしまいます。
2つの1対多に分解する
解決策は、あいだにテーブルを1つ挟むことです。これを中間テーブルと呼びます。
order_items は「どの注文に、どの商品が、何個」を1行で表します。多対多が、2つの1対多に分解されました。外部キーはどちらも多の側、つまり order_items に置かれます。
多対多を見つけたら中間テーブルを作る。これは考えるまでもなく機械的に決まります。
中間テーブルは、ただの繋ぎではない
ここが大事なところです。order_items には quantity と unit_price が入ります。
「この注文でこの商品を3個買った」という個数は、注文だけの性質でも商品だけの性質でもありません。組み合わせに対して初めて意味を持つ値です。こういう値の置き場所は中間テーブルしかありません。
unit_price も同じです。商品の現在価格は products.price にありますが、注文したときの価格は別物です。あとで値上げしたときに過去の注文金額が変わってしまっては困るので、注文の時点の価格を order_items に写し取ります。
一見すると同じ値を2か所に書いているように見えますが、これは冗長ではありません。「いまの定価」と「そのとき売った値段」は、別の事実だからです。
主キーは組み合わせ
同じ注文に同じ商品が2行あると、どちらが本当の個数か分からなくなります。PRIMARY KEY (order_id, product_id) と複合主キーにして防ぎます。
用意済みの投入と金額集計を読む
INSERT INTO は行を入れる文です。VALUES の括弧をカンマで並べると複数行を入れられます。
SQL クエリ
INSERT INTO rental_fees (loan_id, days, daily_fee) VALUES
(7, 2, 100),
(7, 3, 200);
SELECT loan_id, count(*) AS lines, sum(days * daily_fee) AS amount
FROM rental_fees
GROUP BY loan_id
ORDER BY loan_id;sum は値の合計です。各行で日数と日額を掛けてから足すので、例の合計は800です。演習の投入と集計は用意済みで、書くのは中間表の制約です。
手を動かす
order_items を作って、注文明細を入れ、注文ごとの合計金額を出します。
多対多を分解する中間テーブル order_items を作ってください。
order_id integer NOT NULL、product_id integer NOT NULL、quantity integer NOT NULL、unit_price integer NOT NULL- 主キーは
(order_id, product_id)の複合主キー - 外部キーは
order_items_order_fkがorders (id)を、order_items_product_fkがproducts (id)を参照する
作ったら、次の明細を入れてください。(order_id, product_id, quantity, unit_price) の順です。
(1, 1, 1, 128000)、(1, 2, 2, 3200)、(2, 2, 1, 3200)、(2, 3, 1, 8900)、(3, 1, 2, 128000)
最後に、確認用の SQL が order_items に付いた制約を出します。外部キーは名前と列と参照先の表、主キーは列の組で、item_rows と item_total に入った明細の行数と金額の合計 (quantity と unit_price を掛けて足したもの) が出ます。主キーや外部キーが欠けていると、正しい答えと合いません。用意した明細の INSERT と確認用の SQL は書き換えずに残してください (明細の値の入れ替えや確認用の SQL の書き換えは、採点では見分けきれません)。
通常の列、明細の投入、確認用の SQL は用意済みです。多対多を守る主キーと2本の外部キーを定義します。
主キーと外部キーは、表の終わりに 1 つ 1 行で書きます。主キーは PRIMARY KEY (列, 列)、名前付きの外部キーは CONSTRAINT 名前 FOREIGN KEY (列) REFERENCES 表 (列) の形です。前の「つくる ER図v1」で orders の表の終わりに書いた形と同じです。行の終わりのカンマは、表の最後の行には付けません。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 3 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL REFERENCES stores (id),
customer_id integer NOT NULL REFERENCES customers (id)
);
INSERT INTO stores VALUES (1, '渋谷店'), (2, '梅田店');
INSERT INTO customers VALUES (1, '田中あかり'), (2, '佐藤けんじ');
INSERT INTO products VALUES
(1, 'ノートPC', 128000),
(2, 'マウス', 3200),
(3, 'キーボード', 8900);
INSERT INTO orders VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1);
期待される出力
| constraint_type | constraint_name | columns | refers_to | item_rows | item_total |
|---|---|---|---|---|---|
| FOREIGN KEY | order_items_order_fk | order_id | orders | 5 | 402500 |
| FOREIGN KEY | order_items_product_fk | product_id | products | 5 | 402500 |
| PRIMARY KEY | order_id, product_id | 5 | 402500 |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い