先に謝りますが、「主キー駆動設計」という新たな設計手法を編み出したわけではありません。ドメイン駆動設計が流行っているので真似てみただけです...ｽﾐﾏｾﾝ
FUJITSU Advent Calendar 2018 20日目の記事は私 ゆきはらが担当します。
19日目は「micro_mruby_for_arduino_uno を使って Arduino Uno の上で動くアプリケーションを Ruby で書く」で、21日目は「NatureRemoで家の温湿度を集めて分析する話」となっています。
#はじめに
##なぜこのテーマか
本当はテーブル設計のコツやアンチパターンに関する記事を書くつもりだったのですが、主キーについて書き始めると意外に奥が深かったのでそのまま主キー特集にしてしまいました。
とはいえ、テーブル設計において主キーは非常に重要な要素であり「主キー駆動設計」というのはあながち間違いではないと思っています。
##対象読者
- 若手ITエンジニア
- 基本/応用情報技術者試験を勉強している方々
##注意事項
記事の内容はすべて個人の見解であり、会社・組織を代表するものではありません。
#主キーとは何か（おさらい）
##主キーの定義
ご存じの方が多いとは思いますが、改めて見てみましょう。
まずは、データベース界で著名なミックさんに聞いてみましょう。
主キーは、テーブルにおいて必ず一つ存在しなければならず、かつ一つしか存在しません。主キーとは、その値を指定すれば、必ず1行のレコードを特定できるような列の組み合わせのことです。
出所：ミック『達人に学ぶDB設計徹底指南書 ~初心者で終わりたくないあなたへ~』
Weblioさんにも聞いてみました。
**主キー（しゅキー、英語：primary key）とは、関係データベースにおいて、組（レコード）の識別子として利用するのにもっとも好ましいものとして、関係（テーブル）毎にただ一つ設計者により選択・定義された候補キーをいう。**つまり、関係に格納されたレコードを一意に識別するための属性（列、アトリビュート）またはその集合のうち、そのために通常利用されるべき特定の一つをいう。
出所：Weblio辞書 主キー
まとめると主キーとは、**「関係データベースのテーブルにおいて、ある1行を一意に識別するための列の組合せ」**のことです。
日常のなかでいえば、学籍番号や従業員番号、マイナンバーなどが主キーとして扱われていることが想像できます。
ただ、主キーはあくまでも1つのテーブルに対して設計者がただ一つ選択・定義した値であり、実際にあるシステムのあるテーブルで主キーとして使われてるかはテーブル定義を見なければわからないためです。
##主キーと候補キー
Weblioの説明の中に「関係（テーブル）毎にただ一つ設計者により選択・定義された候補キー」とあります。
候補キーというのは、主キーと同様、テーブルのある1行を一意に識別する列の組合せのことですが、これは1つのテーブルに複数存在しうるのです。
ある会社の顧客テーブルを考えてみましょう。
- 顧客（顧客ID, 氏名, 電話番号, メールアドレス, 住所）
例えば、顧客IDを一意になるように機械的に割り振るのであれば、顧客IDは当然主キーになりえます。
また、業務の運用ルールとして「メールアドレスは個人間で使い回しなし＋登録は必須とする」と定義されているのであればメールアドレスは"形式的には"主キーになりえます。
つまり、顧客IDとメールアドレスは候補キーです。この前提のもと、データベース設計者が**顧客IDを主キーとして定義すれば、顧客IDは候補キーかつ主キーとなるのです。**ちなみに、主キーとして定義されなかった候補キーは代替キーと呼びます。
| キーの種類 | 役割 | 存在個数 /1テーブル |
|---|---|---|
| 主キー | 行を一意に識別する | 1つだけ |
| 候補キー | 行を一意に識別する | 複数 |
| 代替キー | 行を一意に識別する | 複数 （主キーを除く） |
##主キーとして定義する方法
主キーとして定義するには、DBMS(Database Management System)に対してDDL(Data Definition Language)を発行する必要があります。
通常はテーブル作成時に主キーを設定します。以下の例ではcustomer_idを主キー(=Primary Key)として設定しています。
create table customers (
customer_id integer not null
, customer_name varchar not null
, phone_number integer
, email varchar not null
, address varchar
, primary key (customer_id)
) ;
このDDL発行をもって、customer_idは主キーとしての運命を歩むことになります。具体的には、**主キーに対して主キー制約が課されます。**すでに存在するcustomer_idと同じ値の行を挿入しようとすると制約違反が生じるようになるのです。
#主キーに求められる条件
主キーが具備すべき性質について説明します。
DBMSとしては、主キーには「一意性」と「NULLでないこと」を要求します。具体的には「一意性制約*1」と「NOT NULL制約*2」として課すことでこれを実現します。つまり、こういうことです。
主キー制約 = 一意性制約 + NOT NULL制約
*1 PostgreSQL Documentation - 5.3.3. Unique Constraints
*2 PostgreSQL Documentation - 5.3.2. Not-Null Constraints
##一意性制約とは何か
対象となる列の組合せが一つのテーブル内で重複することを許さないようにする制約です。
##NOT NULL制約とは何か
対象となる列の組合せの一部または全部がNULLとなることを許さないようにする制約です。
一意に識別するという主キーの役割を踏まえると主キーがNULL(=不定)となることは許されないため、主キーはNOT NULLであることを要求されます。
##主キー制約と一意性制約の違い
もうお分かりかと思いますが、NULLを許容するかしないかの違いです。
| 制約の種類 | 役割 | 対象列のNULLを許すか |
|---|---|---|
| 主キー制約 | 対象列の重複を許さない | NULLを許さない |
| 一意性制約 | 対象列の重複を許さない | NULLを許す |
##実はまだある主キーの条件？
さきほど「DBMSとしては、主キーには...」と記載しましたが、システムとして運用するうえで主キーが満たすべき条件が他に存在するのです。
それが**「不変性」**です。つまり、主キーの値はその行が挿入されてから同じ値であり続けることが要求されます。
kwatchさんからのコメントを踏まえ、「永続性」から「不変性」に表現を変更しました。(2019/01/25)
先ほどの例では、メールアドレスは主キーになりうると言及しましたが、実際の運用を想像すると間違いなく「変更される」ことが予想されますね。
その意味でメールアドレスは主キーとしてはふさわしくありません。また、「変更される」ことが一見ないような値でも主キーとして定義するのは避けたほうがよいです。
例えば、商品コードでもシステムとして一貫して管理されているものならよいですが、業務部門の人たちがその値を管理しているようなものなら主キーにするのは避けたほうがよいと考えます。
このような事例もあります。
岩波文庫がISBNコードを上書き使用している件について
##なぜ主キーは不変性を持たなければならないか
主キーを変更すると様々な問題が起きてしまいます。
**主キーを変更する場合、その主キーを参照している他のテーブルをすべて更新しなければなりません。**これは、データとしての整合性を確保するためです。関連するテーブルの洗い出し、更新にかかる時間と高負荷などを考えると好ましくありません。
そして何より、そのシステムが外部のシステムと連携していた場合、主キーの変更はさらに高難易度・高コストとなり、現実的ではありません。このような理由から、主キーは変更されない（不変性を持つ）ものである必要があるのです。
#不変性を持たせるための主キー設計
そもそも、主キーの不変性を阻害する要因は何なのでしょうか。それは主に、システム化の対象となる業務のルール変更や外部のルール変更などが考えられます。つまり、そのシステムの外部環境に要因があるのです。
このことを踏まえ、主キーをシステムの内部的な値にしてしまえば不変性を欠くことはなくなります。このために主キーとして採用すべき列を**代理キー(Surrogate Key)***3と言います。
代理キーは業務上の意味を持たない機械的な値で、一方で値自体がシステム化対象において意味を持つキーを自然キーと言います。
| 自然キー | 代理キー | |
|---|---|---|
| 意味 | 値自体に意味を持つ*4 | 値自体に意味を持たない |
| 主キー例（受注明細） | 受注日, 顧客番号, 商品コード | 受注明細ID |
| 値の例 | 20181219, 152291, B02005 | 1000024 |
*3 technopedia - Surrogate Key
*4 値自体に意味を持つというのは、システム化対象となる業務等において意味を持つという意味です
##自然キーと代理キー
###自然キーのメリット
-
わかりやすい
値それ自体に意味を持つため、例えばSQLから業務ルールが把握することができます。
###自然キーのデメリット
-
キー構成変更時の影響が大きい
当該キーを参照する別のテーブルのキー（外部キー）がある場合はそれらもすべて一括して変更する必要があります。 -
SQLが複雑になりバグが仕込まれやすくなる
例えば、主キーが5列から構成されるようなケースではSQLに記述する量も多くなり可読性の低下を招きます。結果としてバグを作りこむ可能性が高くなります。
###代理キーのメリット
-
業務ルールや外部のルールの変更に強い
システムの外部環境からは独立して生成される値なのでルール変更を気にする必要がありません -
SQLがシンプルになる
主キーは1列のみとなるため、非常にシンプルで可読性の高いSQLを作ることができます。
###代理キーのデメリット
-
わかりづらい
値自体は意味を持たないため、逆に業務的なルールが把握しづらくなります。
##代理キーの実現手段
DBMSの力を借りて自動採番するのが簡単で確実な方法だと思います。
以下の例(PostgreSQL)では「serial」を使って実現しています。MySQLではauto_incrementがありますね。採番に関してはDBMSによって記法や仕組みが異なるのでご注意ください。
MySQL: 3.6.9 Using AUTO_INCREMENT
PostgreSQL: Using PostgreSQL SERIAL To Create Auto-increment Column
create table customers (
customer_id serial
, customer_name varchar not null
, phone_number integer
, email varchar not null
, address varchar
, primary key (customer_id)
) ;
##代理キーを主キーとする際の注意点
代理キーを主キーとする際に必ず実施すべきことがあります。
それは、**「自然キーに対して一意性制約を課すこと」**です。
代理キーを主キーにすれば主キーとしては不変性も保ててOKなわけですが、本来主キーになるはずだった自然キーに対して一意性制約を課さないと、業務ルール上本来一意にすべき値が重複した状態を許すことになり、データとして正しくない状態になってしまうためです。
テーブル定義書などのドキュメントにも自然キーを明示するとよいですね。
#最後に
この記事を読んで主キーに対する理解が少しでも深まったのであれば幸いです。
弊社のアドベントカレンダーは二つありますのでお時間あるときにぜひお読みください！
FUJITSU Advent Calendar 2018
FUJITSU その2 Advent Calendar 2018
#参考資料