[関係データベース]スーパーキー、候補キー、主キー、代替キー

スーパーキーと候補キーの違いがよくわからなかったので、関係データベースにおけるキーについてまとめてみました。

用語について

関係データベースの用語で説明しますが、データベースでどの用語に対応するかについてまとめておきます。

• 関係（リレーション） → 表（テーブル）
• タプル → 行（レコード）
• 属性 → 列（カラム）
• 空値 → NULL値

各キーの概念の階層化

これから説明するスーパークラス、候補キー、主キー、代替キーは、より広い概念（上位）とより狭い概念（下位）といった階層構造の関係があります。

これらを図示すると以下のようになります。

スーパーキー
└── 候補キー
    ├── 主キー
    └── 代替キー

上位の概念から下位の概念の順に説明していきます。

各キーについて説明する際の具体的な例として、関係”社員”

社員(社員ID, 社員番号, 氏名, 部署, メールアドレス)

を使用します。

スーパーキー（Super Key）

今回説明するキーの中ではもっとも広い概念です。関係のタプルを一意に識別できるすべての属性や属性の組み合わせがスーパーキーです。後述の候補キーとは異なり、余分な属性が含まれてもよいという特徴があり、テーブル内で一意性を保証できる組み合わせであればすべてスーパーキーになります。

関係”社員”の場合、

• {社員ID}
• {社員番号}
• {メールアドレス}
• {氏名, メールアドレス}
• {社員番号, 部署}
• ・・・

などがスーパーキーになります。

候補キー（Candidate Key）

スーパーキーの中でも、最小限の属性で一意性を保証できるキーが候補キーです。余分な属性がないため、スーパーキーよりも狭い概念になります。後述の主キー／プライマリーキーとの大きな違いは空値を許すという点です。

関係”社員”の場合、

• {社員ID}
• {社員番号}
• {メールアドレス}

が候補キーになります。たとえば、{社員ID, 氏名}はスーパーキーですが、氏名がなくても一意性を保証できるため、候補キーにはなりません。

主キー／プライマリーキー（Primary Key）

主キーは、候補キーの中から選んだ１つの候補キーです。前述の候補キーとの違いとして、空値（NULL値）を許しません。

関係”社員”の場合、

• {社員ID}

が一意かつ空値（NULL値）を許さないため、主キーに適しています。

代替キー／代用キー（Alternate Key）

候補キーのうち、主キーとして選ばれなかったキーです。代替キーは主キーとして選ばれなかった候補キーであるため、空値を許します。

候補キーのうち、空値を許さないキーが1つしかなければ、そのキーが主キーになり、それ以外が代替キーになりますが、主キーとして選択可能なキーが複数ある場合はどれを主キーにするかは設計上の判断次第になるため、必ずこれが主キーになるといったルールがあるわけではありません。

関係”社員”の場合、

• {社員番号}
• {メールアドレス}

が代替キーになります。

ナチュラルキーとサロゲートキー

キーの分類には、前述の階層構造と直交する「ナチュラルキー」「サロゲートキー」という考え方があります。

ナチュラルキー／自然キー（Natural Key）

ナチュラルキーとは、データそのものに基づいて一意性を保証するキーです。すなわち、すでに現実世界で意味を持っているデータをプライマリーキーとして使用します。

関係”社員”の場合、

• {社員番号}
• {メールアドレス}

がナチュラルキーになります。今回の例にはありませんが、SSN（社会保障番号）、マイナンバー（個人番号）なども社員を一意に特定できるナチュラルキーになります。

サロゲートキー／代理キー（Surrogate Key）

一方、サロゲートキーとは、データベース内で独自に生成され、現実世界のデータとは直接の関係がない一意なキーのことです。主に整数値（自動増分のID）、GUID（グローバル一意識別子）、UUID（ユニバーサル一意識別子）などが使用されます。

関係”社員”の場合、

• {社員ID}

がサロゲートキーになります。これについては少しわかりづらいので順を追って説明します。

現実世界の社員を分析した結果作成した関係”社員”が

社員(社員番号, 氏名, 部署, メールアドレス)

だったとします。当然ですが、企業においては社員番号で社員を一意に識別することができます。概念データモデルでは{社員番号}を主キーにすることで特に問題なさそうですが、次のケースを考えてみます。

まず、関係”社員”に

社員(社員番号, 氏名, 部署, メールアドレス, 入社年度)

のように属性入社年度を追加します。

次に定年退職後嘱託社員としてシステムに再登録することことを考えてみます。このとき、社員番号は変えずに入社年度は元々の入社年度の情報を残しつつ、嘱託社員として入社したときの入社年度を記録したいとしましょう。具体的には

('S001', '山田太郎', '総務部', 'taro@example.com', 1964)

という社員がいる場合、この社員が2024年に嘱託社員として再雇用された場合、

('S001', '山田太郎', '総務部', 'taro@example.com', 1964)
('S001', '山田太郎', '総務部', 'taro@example.com', 2024)

のようにデータを登録したい場合、社員番号は関係”社員”でタプルを一意に特定するキーとは使用できなくなります。（普通は嘱託社員として再雇用した場合、社員番号は変えることが多いと思いますが、ここでは変えない運用を想定しています）

この場合、

社員(社員番号, 連番, 氏名, 部署, メールアドレス)

のように、属性連番を追加して{社員番号, 連番}で一意になるようにすることもできますが、属性社員IDというタプルを一意に特定する属性を追加する方法もあります。この社員IDはタプルを一意に特定する以外の意味を持たないため、サロゲートキー（ナチュラルキーの代理で使われるキー）とよばれます。

まずはナチュラルキーから検討する

ここではデータベースの用語で説明します。

プロジェクトの方針によってはすべてのテーブルにサロゲートキーを設定する場合もありますが、ナチュラルキーを使いつつ、どうしてもナチュラルキーが使えない場合のみサロゲートキーを使用するという方針もあります。具体的には以下のような場合です。

• ナチュラルキーではレコードを一意に特定できない
• レコードインサート時にナチュラルキーが設定されないケースがある
• 要件や処理の都合でナチュラルキーが一意にならない／一意にならない瞬間がある

主キーにすることで、NOT NULL制約と一意性制約が付与されるため、同じ値が存在しないことを保証できますが、前述のように社員番号ではなく社員IDを主キーとして使用すると、社員番号が重複してしまう可能性があるという問題が起こるため、1件だけ取得したつもりが2件以上返ってきてしまうといった問題につながります。対策として一意性制約を付与するという方法がありますが、意外と忘れがちだったります。

こういったことから、基本的にはナチュラルキーを主キーにできないかを検討し、どうしても難しい場合はサロゲートキーを使用するのがよいでしょう。

外部キー（Foreign Key）

今回の内容とは関係ありませんが、外部キーというキーもあります。

外部キーは、ある関係の候補キーの属性または属性の組を参照するキーです。外部キーは、関係間の関係性を定義し、データの整合性を保証する役割を果たします。

• 関係間の関係性をあらわす
  ある関係が他の関係のデータを参照することによって、関係間にリレーションを形成します。
• 一貫性の保証
  参照元の関係（親）に存在しないデータを参照することができなくなります。

まとめ

関係データベースでは、

• スーパーキー
• 候補キー
• 主キー
• 代替キー

というキーがあり、それらは階層化された関係を持ちます。また、これらのキーと直交する概念として、

• ナチュラルキー
• サロゲートキー

というものもあります。

これらの用語の違いを理解することで、キーの設計を円滑に進められるでしょう。