複合キー
組み合わせで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列必要です。
CREATE TABLE order_item_serials (
order_id integer NOT NULL,
product_id integer NOT NULL,
serial text NOT NULL,
FOREIGN KEY (order_id, product_id) REFERENCES order_items (order_id, product_id)
);さらにその下に孫テーブルができれば、3列、4列と増えていきます。複合主キーは下に伝染します。
もう1つの案
そこで、明細にも id を振る案が出てきます。
CREATE TABLE order_items (
id integer PRIMARY KEY,
order_id integer NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL,
CONSTRAINT order_items_uk UNIQUE (order_id, product_id)
);主キーは id 1列なので、子テーブルは order_item_id を1列持つだけで済みます。そして UNIQUE (order_id, product_id) を別に張ることで、組み合わせの重複は変わらず防げます。
主キーを複合キーにするのをやめても、複合の一意制約は残せる。 ここが大事なところです。守りたかったルールは UNIQUE が守り続けます。
どちらを選ぶか
判断は次のとおりです。
| 状況 | 選ぶもの |
|---|---|
| 子テーブルができる見込みがある | サロゲートキー + UNIQUE |
| 中間テーブルで、子ができる見込みが無い | 複合主キー |
迷ったらサロゲートキー案が無難です。一意性は UNIQUE で守れるので、失うものが無いからです。ただし UNIQUE を書き忘れると重複が入り放題になるので、複合主キーをやめるときは必ずセットで書きます。
手を動かす
注文明細をサロゲートキー案に作り替えて、制約が3種類そろうことを確かめます。
テーブル構造
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 |