8-2 代理キー 〜主キーが役に立たないとき
RDBのテーブルは主キーが必要だが、主キーを決めるのが容易ではないことがある
主キーが決められない、または主キーとして不十分なケース
1️⃣ 入力データに主キーにできるような一意キーが存在しない
これは最も単純だが、あってはならないケース
2️⃣ 一意キーはあるが、サイリクックに使い回される
これは、主キーの値を全て使い切ってしまったときに、既存の値を使うケースで起きる
例
廃止された市町村コードを再利用する
table:市町村
市町村コード 市町村名 人口（人）
A000 A市 100000
A001 B市 100000
......
A999 Z市 111000
⬇️ 市町村を追加したいが、主キーの桁数を拡張できない
table:市町村
市町村コード 市町村名 人口（人）
A000 A市 100000
A001 Q市 120000
......
A999 Z市 111000
主キーを使った履歴管理ができない
3️⃣ 一意キーはあるが、途中で指す対象が変化する
例
市町村が合併された
table:市町村
市町村コード 市町村名 人口（人）
A000 A市 100000
A001 B市 100000
...... ...... ......
A999 Z市 111000
⬇️
table:市町村
市町村コード 市町村名 人口（人）
A000 AB市 220000
...... ...... ......
A999 Z市 111000
主キーを使った履歴管理ができない
代理キーによる解決
主キーが決められない時の解決手段として利用されるのが代理キー（サロゲートキー）
例
table:市町村
市町村管理コード 市町村コード 市町村名 人口（人）
0 A000 A市 100000
1 A001 B市 100000
..... ....... ...... ......
9999 A999 Z市 111000
前述のパターン2, 3の問題を解消できる
代理キーは便利だが、一般的な原則としては、極力代理キーの使用を避ける
避けるべき理由は、エンティティの定義では不要なキーであるため
自然キーによる解決
代理キーを使わずに自然キーで解決する方法は2種類
1️⃣ タイムスタンプ
いつ時点のデータであるかを示すカラムを追加する
例
table:市町村
年度 市町村コード 市町村名 人口（人）
2020 A000 A市 100000
2020 A001 B市 100000
2021 A000 A市 110000
2021 A001 B市 110000
2022 A000 A市 100000
2022 A001 Q市 150000
パターン2, 3の問題を解消できるかつ、エンティティの定義でも意味のあるキーとなっている
レコード数が増えやすいのは欠点
2️⃣ インターバル
データの有効な期間を示すカラムを追加する
例
table:市町村
開始年度 終了年度 市町村コード 市町村名 人口（人）
2000 9999 A000 A市 100000
2000 2021 A001 B市 100000
2022 9999 A000 Q市 110000
パターン2, 3の問題を解消できるかつ、エンティティの定義でも意味のあるキーとなっている
タイムスタンプの欠点も克服している
タイムスタンプ方式よりSQL文の条件は複雑になる
オートナンバリングの是非
やむを得ず代理キーを使うこともある
代理キー実装でしばしば利用される方法がオートナンバリング
オートナンバリングを実現する手段はデータベースの機能を使うかアプリケーションの機能を使うか
オートナンバリングの実現方法 1️⃣ 〜データベース機能
2種類
シーケンスオブジェクト
一意な連番を払い出すオブジェクト
SELECT文で一意な連番を取得できる
code:sql（PostgreSQL）
CREATE SEQUENCE test_seq
START 1;
SELECT nextval('test_seq');
DBMSによってはサポートしていない
排他制御により、同時アクセスが集中すると性能遅延が発生する
ID列
一意な連番を払い出すデータ型
DBMSによって実装が違う
PostgreSQL
整数型のGENERATED ALWAYS AS IDENTITYオプション
MySQL
整数型のAUTO_INCREMENTオプション
オートナンバリングの実現方法 2️⃣ 〜アプリケーション側で機能
採番テーブルを自前で実装する
排他制御を利用したプログラムを作成する必要がある
プログラムが複雑になったりと開発コストがかかる
排他制御により、同時アクセスが集中すると性能遅延が発生する