キーの階層 (スーパーキー・候補キー・主キー)

「行を見分けるための鍵」にはいくつか種類がある

テーブルの中から特定の 1 行を指し示すには、他の行と区別できる目印が必要になる。 この目印になる列 (または列の組み合わせ) を キー と呼ぶ。

ややこしいのは、キーには 用途と性質による名前 がいくつかあること。 大きい枠から順に「スーパーキー ⊃ 候補キー ⊃ 主キー」の入れ子構造になっている。 以下の集合図と説明で全体像を掴んでおこう。

「極小」ってどういうこと?

候補キーの説明で「極小」という言葉が出てくる。これは「その中のどれか 1 つでも列を削ったら、もう行を一意に見分けられなくなる」ぎりぎりの状態を指す。

例えば (注文ID, 商品ID) という 2 列セットが候補キーだとする。 このセットから 注文ID だけを削って (商品ID) にすると、 同じ商品が複数の注文で買われていた場合に行が一意にならなくなる。 逆に 商品ID だけを削って (注文ID) にしても、同じ注文で複数商品を買っていれば一意にならない。 つまり (注文ID, 商品ID) は 「これ以上減らせない最小の組み合わせ」 になっている、というのが「極小」の意味。

ちなみに、この (注文ID, 商品ID) に 数量 を足した (注文ID, 商品ID, 数量) も 「行を一意に見分けられる列の組み合わせ」ではあるが、数量 は無くても一意に見分けられるので「極小」ではなく、 候補キーではなくスーパーキー止まりになる。

複数の列を組み合わせるキー (複合キー) と、他テーブルを参照するキー (外部キー)

キーは 1 つの列とは限らない。複合キー (composite key): 2 つ以上の列を組み合わせて初めて 1 行を見分けられる場合のキー。 よく出てくるのは「注文と商品を組み合わせて 1 つの明細行を表す」ような中間テーブルで、(注文ID, 商品ID) という 2 列セットが主キーになる。

外部キー (foreign key): 別テーブルの主キーを指し示すために持つ列。 たとえば「受注明細」テーブルに置いた 注文ID は「受注」テーブルの主キーを指し、商品ID は「商品」テーブルの主キーを指している。 テーブルを分割した時に生まれる「あっちのテーブルのこの行を指してる」という参照関係を保つのが役割。

実際にはこんな形になる。

受注明細テーブルの (注文ID, 商品ID) は 複合キー (2 列セットで主キー)。 同時に、注文ID は 受注 テーブルへの外部キー・商品ID は 商品 テーブルへの外部キーになっている。

覚えておく用語: 非キー属性

次の 第2正規形・第3正規形から「非キー属性」という言葉が頻繁に出てくる。 字面通り「キーに含まれない列」のこと。

たとえば (社員ID, 氏名, 部署ID, 部署名) というテーブルで、 候補キーが 社員ID だけなら、氏名・部署ID・部署名の 3 つがすべて非キー属性。 「非キー属性が他の何にどう決まっているか」を見ることで、次の正規形に進める余地があるかを判断する。

主キーの選び方 (実務のコツ)

候補キーが複数あるときに、どれを主キーに選ぶかは実務でよく迷う。目安は以下の 4 つ。

• 値が変わらないもの: 主キーの値が途中で変わると、それを参照している外部キーも全部書き直すことになる。だから将来変わらない属性が望ましい。
• NULL にならないもの: NULL だと「値が無い」ので行を特定できない。主キーには NULL 不可の列を選ぶ。
• 短くて扱いやすいもの: 長い文字列や複数列のセットは、参照する他のテーブルからも扱いにくくなる。
• ちょうどいい業務キーが無ければ「代理キー」: 上記を満たす業務属性が無いなら、 連番 ID など「業務的な意味は無いけど、行を見分けるためだけの ID」を作って主キーにする。これを サロゲートキー (surrogate key) と呼ぶ。