複合キー
組み合わせで1件を決める
注文明細の主キーは (order_id, product_id) でした。1つの列では1件を特定できず、2つ合わせて初めて決まります。これが複合主キーです。
複合主キーが自然に出てくるのは、中間テーブルです。inventories も (store_id, product_id) で1件が決まります。「どの店のどの商品」という組み合わせそのものが、そのテーブルの主語だからです。
複合主キーの効き目
複合主キーには2つの効き目があります。
組み合わせの重複を防ぎます。同じ注文に同じ商品の明細が2行できると、どちらが本当の個数か分からなくなります。複合主キーがあれば、その2行目の挿入は失敗します。
設計の意図が読めます。 PRIMARY KEY (order_id, product_id) と書いてあれば、「1つの注文に同じ商品は1行しか出てこない」という業務ルールが、テーブル定義を見ただけで分かります。
複合主キーの面倒さ
面倒さもあります。子テーブルができたときです。
注文明細ごとにシリアル番号を記録したくなったとします。order_item_serials は明細を指す必要がありますが、明細を指すには2列必要です。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
CREATE TABLE loan_book_serials (
loan_id integer NOT NULL,
book_id integer NOT NULL,
serial text NOT NULL,
FOREIGN KEY (loan_id, book_id) REFERENCES loan_books (loan_id, book_id)
);孫テーブルも複合主キーを引き継ぐと、3列、4列と増えていきます。複合主キーは下に伝染します。
もう1つの案
そこで、明細にも id を振る案が出てきます。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
CREATE TABLE loan_books (
id integer PRIMARY KEY,
loan_id integer NOT NULL,
book_id integer NOT NULL,
quantity integer NOT NULL,
CONSTRAINT loan_books_uk UNIQUE (loan_id, book_id)
);主キーは id 1列なので、子テーブルは order_item_id を1列持つだけで済みます。そして UNIQUE (order_id, product_id) を別に張ることで、組み合わせの重複は変わらず防げます。
主キーを複合キーにするのをやめても、複合の一意制約は残せる。ここが大事なところです。守りたかったルールは UNIQUE が守り続けます。
どちらを選ぶか
判断は次のとおりです。
| 状況 | 選ぶもの |
|---|---|
| 子テーブルができる見込みがある | サロゲートキー + UNIQUE |
| 中間テーブルで、子ができる見込みが無い | 複合主キー |
迷ったらサロゲートキー案が無難です。一意性は UNIQUE と 2 列の NOT NULL で守れるので、失うものはほとんどない (id 列と索引の分の容量は増えます) からです。ただし UNIQUE を書き忘れると重複が入り放題になるので、複合主キーをやめるときは必ずセットで書きます。
内部の必須制約を除く確認
PostgreSQL は NOT NULL も制約として記録します。この教材の確認では、名前が not_null で終わる内部制約を外して業務制約だけを数えます。
SQL クエリ
SELECT constraint_type, count(*) AS rules
FROM information_schema.table_constraints
WHERE table_name = 'loan_books'
AND constraint_name NOT LIKE '%not_null'
GROUP BY constraint_type
ORDER BY constraint_type;LIKE の % は任意の文字列に一致します。NOT LIKE は、その形に一致しないものだけを選びます。名前の形で除く方法なので、別の名前の制約を巻き込むことがあります。演習の確認は用意済みです。
手を動かす
注文明細をサロゲートキー案に作り替えて、制約が3種類そろうことを確かめます。
order_items をサロゲートキー案で作り直してください。
- いまの
order_itemsをDROP TABLEする - 次の定義で作り直す
id integer PRIMARY KEYorder_id integer NOT NULL、外部キーorder_items_order_fkがorders (id)を参照するproduct_id integer NOT NULL、外部キーorder_items_product_fkがproducts (id)を参照するquantity integer NOT NULL- 一意制約
order_items_ukが(order_id, product_id)の組み合わせを一意にする
information_schemaから.table_constraints order_itemsの制約のうち、constraint_nameが'%not_null'で終わらないものを、constraint_typeごとに数える。constraint_typeと件数cntの2列をconstraint_typeの順に取り出す
再作成の土台は用意済みです。主キーと業務上の一意性を分け、2本の参照を守ります。 ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL REFERENCES stores (id)
);
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders (id),
product_id integer NOT NULL REFERENCES products (id),
quantity integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO stores VALUES (1, '渋谷店');
INSERT INTO products VALUES (1, 'ノートPC'), (2, 'マウス');
INSERT INTO orders VALUES (1, 1), (2, 1);
期待される出力
| constraint_type | cnt |
|---|---|
| FOREIGN KEY | 2 |
| PRIMARY KEY | 1 |
| UNIQUE | 1 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い