タグ: foreign key

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

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

用語について

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

  • 関係（リレーション） → 表（テーブル）
  • タプル → 行（レコード）
  • 属性 → 列（カラム）
  • 空値 → 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）

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

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

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

まとめ

関係データベースでは、

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

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

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

というものもあります。

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

外部キー制約でのON DELETE CASCADEオプションの使い方

外部キー制約でのON DELETE CASCADEオプションの使い方を具体的な例を使って解説します。

ON DELETE CASCADEオプションは親テーブル（参照先のテーブル）を削除するときに子テーブル（参照元のテーブル）も一緒に削除するために使用するオプションになります。

解説に使用するデータベースとテーブルについて

解説に使用するデータベースとテーブルについて説明します。

確認にはOracle Database 19cを使用しています。若干の構文の違いはありますが、他の主要なデータベースでも同様の操作が可能です。

まずは親テーブルを作成します。親テーブルのMAINテーブルは主キーIDのみを持ったテーブルです。

CREATE TABLE MAIN
(
  ID NUMBER(3),
  CONSTRAINT MAIN_PK PRIMARY KEY (ID)
)

次に子テーブルを作成します。子テーブルのSUBテーブルはIDとSEQを主キーに持つテーブルです。

CREATE TABLE SUB
(
  ID  NUMBER(3),
  SEQ NUMBER(2),
  CONSTRAINT SUB_PK PRIMARY KEY (ID, SEQ)
)

ON DELETE CASCADE オプションなしの場合

では、ON DELETE CASCADEオプションを使用しない場合の動作を見てみましょう。

SUBテーブルのIDに外部キー制約を設定します。

SQL> ALTER TABLE SUB ADD CONSTRAINT SUB_ID_FK
   2 FOREIGN KEY (ID) REFERENCES MAIN(ID)

表が変更されました。

では、データを投入して削除時の動作を確認します。

SQL> INSERT INTO MAIN VALUES (1)

1行が作成されました。
SQL> INSERT INTO SUB VALUES (1, 1)

1行が作成されました。
SQL> INSERT INTO SUB VALUES (1, 2)

1行が作成されました。

削除する前にデータを確認しておきましょう。

SQL> SELECT * FROM MAIN

ID
--
 1

1行が選択されました。
SQL> SELECT * FROM SUB

ID SEQ
-- ---
 1   1
 1   2

2行が選択されました。

MAINテーブルのID=1のレコードにSUBテーブルのID=1のレコードが2件ぶら下がっている形になっています。

データが投入できましたので、親テーブルMAINテーブルのレコードを削除してみます。

SQL> DELETE FROM MAIN WHERE ID = 1
DELETE FROM MAIN WHERE ID = 1
*
行1でエラーが発生しました。:
ORA-02292: 整合性制約(USER01.SUB_ID_FK)に違反しました - 子レコードがあります

整合性制約（外部キー制約）で削除に失敗しました。ON DELETE CASCADEオプションがない場合は、子テーブルを削除してからでないと親テーブルを削除できません。

念のためデータが削除されていないことを確認しておきます。

SQL> SELECT * FROM MAIN

ID
--
 1

1行が選択されました。
SQL> SELECT * FROM SUB

ID SEQ
-- ---
 1   1
 1   2

2行が選択されました。

ON DELETE CASCADEオプションありの場合

では、ON DELETE CASCADEオプションをつけた場合の削除の動きを確認します。

すでに設定済みの外部キーを削除しておきます。

SQL> ALTER TABLE SUB DROP CONSTRAINT  SUB_ID_FK

表が変更されました。

今度はON DELETE CASCADEオプション付きで外部キー制約を設定します。

SQL> ALTER TABLE SUB ADD CONSTRAINT SUB_ID_FK
   2 FOREIGN KEY (ID) REFERENCES MAIN(ID)
   3 ON DELETE CASCADE

表が変更されました。

では、もう一度削除を試します。

> DELETE FROM MAIN WHERE ID = 1

1行が削除されました。

今度は削除できました。

削除後のデータを確認します。

SQL> SELECT * FROM MAIN

レコードが選択されませんでした。
SQL> SELECT * FROM SUB

レコードが選択されませんでした。

削除したMAINテーブルのレコードだけでなく、SUBテーブルのID=1のレコードも削除されています。

どういった場合にON DELETE CASCADEオプションを使うか

まず、外部キー制約を使用する場合に限られているということが前提となります。

上記の前提の上で、以下の2つのどちらの効果を狙いたいかで決めるとよいでしょう。

  • 子テーブルにデータがあるときに親テーブルが削除されるのを防ぎたい
  • 親テーブルが削除されるときに子テーブルをまとめて削除したい

もう少し具体的な例で説明します。

ON DELETE CASCADEオプションを使いたくないケース

ON DELETE CASCADEオプションを使いたくないケース、使うべきでないケースとして挙げられるのが、マスタテーブルに対して外部キー制約を設定しているケースです。

例えば、注文テーブルにある顧客番号に対して、顧客マスタの顧客番号への外部キーを設定している場合、ON DELETE CASCADEオプションを設定していると、注文テーブルのレコードの削除によって顧客マスタのレコードが削除される場合があります。
これは望ましい挙動でないことから、マスタテーブルへの外部キー設定の場合はON DELETE CASCADEオプションを使用するべきではありません。

ON DELETE CASCADE オプションを使いたいケース

ON DELETE CASCADEオプションを使いたいケースとして挙げられるのが、子テーブルが親テーブルに対して外部キー制約を設定しているケースです。

例えば、注文テーブルと注文明細テーブルがあり、注文明細の注文番号に対して、注文テーブルの注文番号への外部キーを設定している場合は、ON DELETE CASCADEオプションを設定していると、注文テーブルのレコード削除時に注文明細テーブルのレコードをまとめて削除してくれるため便利です。
ON DELETE CASCADEオプションを設定していない場合は、先に注文明細テーブルの該当する注文番号のレコードを先に削除してからでないと注文テーブルのレコードを削除できないため、ON DELETE CASCADEオプションを使うことが削除を行うプログラムの構造をすっきりとさせることができる効果があります。

まとめ

前述の例をまとめると以下のようになります。

CREATE TABLE 注文
(
  注文番号 NUMBER(10),
  顧客番号 NUMBER(3),
  CONSTRAINT 注文_PK PRIMARY KEY (ID)
)

CREATE TABLE 注文明細
(
  注文番号 NUMBER(10),
  注文明細連番 NUMBER(10),
  CONSTRAINT 注文明細_PK PRIMARY KEY (注文番号, 注文明細番号)
)

CREATE TABLE 顧客マスタ
(
  顧客番号 NUMBER(3),
  CONSTRAINT 顧客マスタ_PK PRIMARY KEY (顧客番号)

-- 注文から顧客マスタへの外部キーではON DELETE CASCADEオプションをつけない
ALTER TABLE SUB ADD CONSTRAINT 注文_顧客番号_FK
FOREIGN KEY (顧客番号) REFERENCES 顧客マスタ(顧客番号)

-- 注文明細から注文への外部キーではON DELETE CASCADEオプションをつける
ALTER TABLE SUB ADD CONSTRAINT 注文明細_注文番号_FK
FOREIGN KEY (注文番号) REFERENCES 注文(注文番号)
ON DELETE CASCADE

このようにON DELETE CASCADEオプションを適切に設定する／しないことでデータを安全に、効率よく管理できるようになります。

モバイルバージョンを終了