物理FK vs 論理FK
外部キーを張れない場所がある
ここまでの FOREIGN KEY は、DB がその場で検査してくれる本物の制約でした。これを物理FKと呼びます。ところが現場のテーブル定義を見ると、明らかに accounts.id を指している account_id という列に、制約が付いていないことがあります。手抜きに見えますが、多くは意図的です。
張れない、あるいは張らない理由は 3 つあります。1 つ目は、親テーブルが別のデータベースにある場合です。サービスを分けて運用していると参照先が別の場所にあるので、REFERENCES では繋げません。2 つ目は、テーブルを複数のサーバに分割している場合です。親と子が別のサーバに載っていると、DB は照合しようがありません。3 つ目は書き込み量です。1 秒に数千行が積まれるログ系のテーブルでは、1 行ごとに親を確かめるコストが無視できなくなります。
こういうときに使うのが、論理FKと呼ばれる運用です。
制約は書かず、名前と約束だけで繋ぐ
SQL クエリ
CREATE TABLE page_view_log (
id BIGINT PRIMARY KEY,
account_id BIGINT NOT NULL, -- accounts.id を指す
path TEXT NOT NULL,
viewed_at TIMESTAMP NOT NULL
);account_id という名前とコメントだけが関係を表しています。DB は何も知らないので、存在しないアカウントの行も黙って受け取ります。
SQL クエリ
INSERT INTO page_view_log VALUES (9001, 99999, '/pricing', NOW());
-- 99999 番のアカウントが無くても通る速いのは、確かめていないからです。
| 見るところ | 物理FK | 論理FK |
|---|---|---|
| 整合性 | DB が保証する | アプリと運用が保証する |
| 書き込み | 検査の分だけ遅い | 速い |
| 親が別 DB や別サーバ | 張れない | 使える |
| 孤児レコード | 発生しない | 発生しうる |
守る責任がアプリと運用に移る
論理FKは「整合性を諦める」ことではありません。守る場所が DB の外へ移るだけです。移った先で何もしなければ、ただの壊れたテーブルになります。
要るものは 2 つあります。1 つは書き込み側の規律で、親の存在を確かめてから子を書く、親を消したら子も始末する、という手順をアプリに持たせます。もう 1 つは定期的な点検で、親に存在しない値を持つ子の行を数えるジョブを回し、見つかったら通知します。孤児が出たということは、削除の順序かアプリのどこかが壊れているという合図なので、件数そのものより「増えていないか」を見ます。
迷ったら物理FKにする
同じデータベースの中で混ぜて構いません。顧客や商品のようなマスタ系は物理FK、アクセスログや監査ログのように大量に積まれる系は論理FK、という分け方が現実的です。
新しく作るテーブルで判断に迷ったら、物理FKにしておきます。論理FKを選ぶ理由、つまり親が別 DB にあるとか書き込みが極端に多いといった事情を具体的に言えないなら、DB に見張らせておくほうが安上がりです。後から物理FKへ移すこともできますが、そのときは既存の孤児を全部片付けてからでないと、制約そのものが張れません。
テーブル構造
CREATE TABLE users (
id INT PRIMARY KEY,
name TEXT NOT NULL
);
-- 論理FKのつもりだったが整合性チェックが甘く orphan が混入したログ
CREATE TABLE event_log (
id INT PRIMARY KEY,
user_id INT NOT NULL,
event_type TEXT NOT NULL
);
INSERT INTO users (id, name) VALUES
(1, '田中'),
(2, '佐藤'),
(3, '鈴木');
INSERT INTO event_log (id, user_id, event_type) VALUES
(1001, 1, 'login'),
(1002, 2, 'login'),
(1003, 1, 'purchase'),
(1004, 99, 'login'), -- orphan
(1005, 100, 'purchase'); -- orphan
期待される出力
| id | user_id | event_type |
|---|---|---|
| 1004 | 99 | login |
| 1005 | 100 | purchase |