かとじゅんの技術日誌

技術の話をするところ

外部キー制約は何も考えずに適用するとよくない

DB設計 DDD

このブログが話題になってますね。制約を付けること自体はよいことだけど、無目的に適用すると害も生じると思います。 無目的という言い方はおかしいな…。外部キー制約をどのように使えばいいのか、逆にどんなときに使うとまずいのかを考えてみたいと思います。

tech.tabechoku.com

例えば、これ。外部キー制約はできるだけ付けるとか、何も考えずに付けるとよくないと思います。

外部キー制約は、可能な限りつけるようにしています。 DBが別れている場合、外部キーはもちろん貼れないのですが、そうでない場合はとにかく何も考えず貼っています。

データベース設計の際に気をつけていること - 食べチョク開発者ブログ

テーブル設計をシミュレーションする

いいたいことの結論はこれ。以上終了なのですが、もう少しわかりやすく書いてみよう。

テーブル設計

以下のテーブルがあるとします。商品の売上を管理するテーブルです。とりあえず、何も考えずに外部キー制約をすべてに適用しています。(論理削除ではなく一旦物理削除で考えましょう。論理削除はこういった境界分析の邪魔になりますので)

  • 売上テーブル
    • 売上ID(PK)
    • 売上日時
  • 売上詳細テーブル
    • 売上詳細ID(PK)
    • 売上ID(FK)
    • 商品ID(FK)
    • 数量
  • 商品テーブル
    • 商品ID(PK)
    • 商品名
    • 単価

f:id:j5ik2o:20200616103927p:plain

想定ユースケース

  • 商品情報を登録・更新・削除する
  • 売上を登録・更新・削除する
    • 売上には売れた商品、数量、金額の売上詳細の概念が含まれる

強い整合性と弱い整合性

ユースケースから考えて、整合性の境界を考えます。整合性には強いものと弱いものがあります。強い整合性は作成・更新・削除するときに一緒に行います。これは「トランザクション整合性」と呼ばれることがあります。弱い整合性は「結果整合性」と呼ばれることがあります。では、上記のテーブルで考えていきます。上記の図の点線はこの整合性の境界範囲を示しています。仮に全部が一つの境界内にあると考えシミュレーションします。

売上・売上詳細のトランザクション境界と外部キー制約

売上を登録するときに、一緒に個々の売上詳細を登録・更新・削除(物理削除)します。トランザクション整合性の境界(トランザクション境界)はどこからどこまでがよいでしょうか。売上には売上詳細の概念が含まれるとしているので、売上と売上詳細は同じ境界のほうが都合がよさそうです。つまり、売上と売上詳細が更新されるときは別個ではなく、不可分な一塊として扱われます。例えば、売上が先に作られて、売上詳細が後から作られることはありません。売上詳細だけが先に削除されることもなく、一緒に削除されます。

さて、売上と売上詳細は不可分な一塊です。ある売上詳細Aが存在するとき、参照する売上Bは必ず存在します。つまり売上詳細Aの売上ID(FK)は存在する売上BのIDを参照します。売上Bが存在しないとき売上詳細Aも存在しません。この外部キー制約は機能しそうですね。データを保護できそうです。

売上・売上詳細と商品は同じトランザクション境界か？

ここからが問題です。商品ID(FK)はどうなんでしょうか。ここに外部キー制約があっても本当に大丈夫でしょうか？ユースケースを見るかぎり、商品と売上(売上詳細を含む)は別々に作成・更新・削除されます。存在する売上詳細Aが削除されるとき、参照する商品Aはまだ削除されません。売上詳細Aの商品ID(FK)から商品AのIDに外部キー制約があると、売上詳細Aは削除できません。また、 ←(この表現は間違いでした。参照している側は削除できますね) 。外部キー制約の関係上、売上詳細が存在することで、商品単体での削除ができません。ユースケースを満たさなくなるので、同じトランザクション境界にできません。(え、普通は商品は削除しないだろうがという方、分かります。とりあえず最後まで読んでほしい)

なので、以下のように境界が別個になるはずですが、外部キー制約があるほうが問題になります。

f:id:j5ik2o:20200616104018p:plain

整合性境界は物理削除で考えたほうがラク

「論理削除なら整合性の境界とか考える必要ないのでは？」という想定質問があるのですが、そんなことはないです。論理削除でも更新する境界がどこからどこまでかを考える必要があります。売上を消したつもりが商品も消されたら問題ですから。削除フラグが連動する範囲が整合性の境界になります。しかし、これを頭の中で整理して考えることが難しい。なので、分析時は物理削除で考えたほうが圧倒的にラクです。

売上・売上詳細と商品は結果整合性を使う

ということで、この場合どう考えるとよいか。トランザクション境界が異なるということはライフサイクルの境界が異なるわけです。売上詳細から参照する商品IDは存在するかもしれないし、すでに削除されているかもしれません。こういった弱い整合性を結果整合性といいます。ここには外部キー制約は適用できません。

また、アプリケーションロジックで以下の売上合計金額を算出する場合、売上詳細からみて商品が結果整合性では、再計算できなくなってしまいます。

  • 売上合計金額=すべての売上詳細金額の合計
  • 売上詳細金額=商品IDの単価×数量

これに対処するには、売上・売上詳細の境界内に再計算するための材料を保持する必要があります。もしくは計算結果を保持する方法もありそうですね。前者だとしたら、更新時にそのときの商品IDの単価をコピーする必要があります。(場合によっては商品名が更新されたり削除されるかもしれないので、商品名のコピーも必要になるかもしれません)

  • 売上テーブル
    • 売上ID(PK)
    • 売上日時
  • 売上詳細テーブル
    • 売上詳細ID(PK)
    • 売上ID(FK)
    • 商品ID(※)
    • 単価(※)
    • 数量
  • 商品テーブル
    • 商品ID(PK)
    • 商品名
    • 単価

f:id:j5ik2o:20200616104057p:plain

これは集約という考え方

このような考え方は、ドメイン駆動設計の集約 を学ぶとわかるようになるはずです。今回はテーブル設計の視点から説明してみましたが、ドメインモデルが整合性の境界を持つと考えるとわかりやすくなるかも。DDDの観点で簡単に言えば、強い整合性境界である集約の内側では外部キー制約に意味があり、集約の外には外部キー制約は不要です。興味がある方はぜひ学んでみたらいいと思います。

エリック・エヴァンスのドメイン駆動設計

エリック・エヴァンスのドメイン駆動設計

  • 作者:Eric Evans
  • 発売日: 2013/11/20
  • メディア: Kindle版
実践ドメイン駆動設計

実践ドメイン駆動設計

ということで、整合性の境界を無視して、外部キー制約を適用することはできません、ということで。

併せて読みたい:

kbigwheel.hateblo.jp

追記:

ブコメで例が悪いと指摘があったので、少し再考してみた。わかりにくいことは認める。言いたいことは結果整合性を求める用途では外部キー制約は使えない。トランザクション整合性のある境界内では使えるという主張だった。

で、商品＝今使える商品という意味で捉えてほしい。商品が廃盤になるとやはり物理削除して、過去に使えてた商品テーブルに移動するかもしれない。実際は、こんな難しいことをせずに、商品は削除せずに「廃盤状態」に遷移できるようにする方法もある。が、何が正しいかは要件による。ここでは仕様の善し悪しを議論したいわけではなく、仮に商品を消すことがある場合、売上詳細から商品ID(FK)は強い整合性を望むので使えないという意図だった。つまるところ、ライフサイクルの境界が異なるのだから、商品IDから商品に到達できるかはそのとき次第なので、以下のような考慮が必要かもしれない、ということ。

とはいえ、もっとよい事例はないかと考えた。Slackのようなチャットサービスで、あるアカウントが投稿したメッセージは他のアカウントでも読める。しかし、ある投稿者のアカウントIDが退会したケースを考えてみよう。忘れられる権利に対応するために、アカウントは物理削除しなければならないとする(物理削除好きやな。まぁ例示のための仮定です)。しかし、退会アカウントのメッセージはタイムラインで、”退会済みアカウント”が投稿したメッセージとして、他のアカウントから閲覧できるものとする。この場合でも、メッセージとアカウントはライフサイクル、整合性の境界が独立している。メッセージからみてアカウントはあるかもしれないしないかもしれない。弱い整合性。この場合は、メッセージのアカウントIDからアカウントのアカウントIDに外部キー制約は適用できない。

  • アカウント
    • アカウントID(PK)
    • メールアドレスなどの個人情報など
  • メッセージ
    • メッセージID(PK)
    • スレッドID
    • メッセージ内容
    • アカウントID(必ずしも存在するとは言えないので、外部キー制約は適用できない)
    • 作成日時
    • 更新日時