はじめに
リンクアンドモチベーションの大塚です。
先日、自分のチームが見ているサービスで、こんな構造のバグに当たりました。
既存の紐付けを一括変更する処理で、「玉突き」で値を入れ替える操作だけが
Duplicate entryエラーで落ちる。
※ ここで言う「玉突き」とは、たとえば3人の担当者が A→B→C→A のように、複数の値が連鎖的にぐるっと入れ替わるパターンのことです
最初は「テストケースが足りなかった」で片付けようとしたのですが、深掘りしてみると、根っこに 「そもそも中間テーブルを UPDATE する発想自体が罠だったのでは？」 という設計上の問いが転がっていました。
この記事では、その「玉突きで壊れたバグ」を題材に、
- 直接の原因
- もっと深い構造的な原因
- なぜ「関連は UPDATE されない」と捉えたほうがいいのか
- アプリケーション層だけで取れる現実的な落とし所
を整理してみます。
なお、本記事のコード例は自分のチームが主に使っている Rails（Ruby on Rails）で書きますが、考え方自体は他のスタックでもそのまま当てはまります。SQL レベルの話に踏み込むので、Rails 知識がなくても問題なく読めるはずです。
題材：プロジェクトとリーダーの紐付け
具体例として、プロジェクト管理ツールを考えてみます。
- プロジェクト には、必ず1人の リーダー が紐づく
- 1人のメンバーは、1プロジェクトのリーダーしか担当できない（兼任不可）
ER的にはこんな感じです。
中間テーブル project_leaders には、業務ルールから以下の2つの UNIQUE 制約が必要になります。
| 制約 | カラム | 意味 |
|---|---|---|
unique_project |
project_id |
各プロジェクトのリーダーは1人 |
unique_member |
member_id |
各メンバーは1プロジェクトしかリーダーになれない |
ここまでは、よく見るスタンダードな設計だと思います。
起きたこと：玉突きで UPDATE が落ちる
期初の組織変更で、外部の人事システムから「リーダー割り当て」の更新ファイルが流れてきます。中身は、たとえばこんな変更だったとしましょう。
| プロジェクト | 旧リーダー | 新リーダー |
|---|---|---|
| PJ-A | Aさん | Bさん |
| PJ-B | Bさん | Cさん |
| PJ-C | Cさん | Aさん |
3人の担当プロジェクトが円環状に 玉突き で入れ替わっています。リーダーの顔ぶれは変わらず、担当プロジェクトだけがシャッフルされる、よくあるパターンです。
これを 「既存レコードを上書きする UPDATE」 で実装すると、こうなります。
# 擬似コード（実装の雰囲気）
ProjectLeader.upsert_all(
rows,
unique_by: :project_id,
update_only: [:member_id]
)
Rails の upsert_all は、内部的には MySQL の INSERT ... ON DUPLICATE KEY UPDATE を発行します。素朴に考えると「project_id をキーにして member_id を書き換えるだけ」なので、何の問題もなさそうに見えます。
ところが、玉突きケースだけが落ちます。
Mysql2::Error: Duplicate entry '...' for key 'unique_member'
直接の原因：衝突解決キーと更新対象が、UNIQUE 制約越しに重なっている
何が起きているかをほどいてみます。
PJ-A の行を Aさん → Bさん に書き換える瞬間、まだ PJ-B の行は Bさん のままです。この瞬間、member_id = B の行が 2つ存在しよう とします。
すると、もう1つの UNIQUE 制約 unique_member (member_id) でぶつかります。
順番を変えれば回避できる、と思うかもしれませんが
実はこのバグ、順番を工夫しても回避できません。3つの UPDATE をどの順で実行するか、すべて試してみます。
| 最初に UPDATE する行 | 起きること |
|---|---|
| PJ-A から（A→B） | この瞬間まだ PJ-B は Bさんのまま → unique_member 違反
|
| PJ-B から（B→C） | この瞬間まだ PJ-C は Cさんのまま → unique_member 違反
|
| PJ-C から（C→A） | この瞬間まだ PJ-A は Aさんのまま → unique_member 違反
|
どの行から始めても、最初の1回の UPDATE で必ず衝突します。これは玉突き（円環状の入れ替え）の構造的な性質で、順番をどう変えても回避できません。
つまり、何が矛盾しているのか
業務ルール（UNIQUE 制約）は、いかなる瞬間も 「各プロジェクトには1人だけリーダーがいて、各メンバーは1プロジェクトのリーダーである」を要求しています。
ところが、玉突きを 行単位の UPDATE で表現しようとすると、最終状態に到達する過程で必ず以下のどちらかの中間状態を経由します。
（一括更新しろ！的なコメントはあるかもしれませんが、一旦例えのために行単位で更新するものとします。）
-
同じメンバーが2つのプロジェクトに同時に紐づいている瞬間（
unique_member違反） - そのメンバーがどこにも紐づいていない瞬間
ON DUPLICATE KEY UPDATE は行単位での INSERT or UPDATE しか表現できないので、この中間状態を回避する手段がありません。
つまり、業務ルールが要求する「いかなる瞬間も成立する制約」と、行単位 UPDATE の表現力との間に、構造的なギャップがある。これが「設計上、避けようがない矛盾」の正体です。
一段深い問題：そもそも「関連」って更新される？
ここで一旦踏みとどまって考えてみます。
「PJ-A のリーダーが Aさんから Bさんに 更新 された」と業務的に表現したくなりますが、これは本当に「更新」なのでしょうか？
業務的に起きていることは、
- 「Aさんが PJ-A のリーダーである」という事実 が、ある日終わった
- 「Bさんが PJ-A のリーダーである」という事実 が、ある日始まった
の2つです。古い関連の終了と、新しい関連の開始。「同じ1つの紐付けレコードが内容を書き換えた」のではなく、異なる2つの事実が、時系列上で隣接しているだけ です。
実は、これはリレーショナルモデルの理論で考えても同じです。リレーション（タプルの集合）における「更新」は、論理的には DELETE → INSERT の合成として導出される派生的な操作で、本来のプリミティブではありません。SQL に UPDATE 文があるのは、行ロックや MVCC、インデックス維持コストといった 実装側の都合 であって、論理モデル上の必然ではないんですね。
業務ドメインから見ても、リレーショナル理論から見ても、
「関連」は更新されない。古い関連の終了と、新しい関連の開始があるだけ。
と捉えるのが筋が良さそうです。今回のバグは、現実世界で起きていない「関連の UPDATE」を、無理やり UPDATE文で表現しようとして構造的に破綻した、と読めます。
パターンA：UPDATE をやめて、DELETE + INSERT にする
ここまでの話を踏まえると、対処は意外とシンプルです。
既存レコードの業務カラムを UPDATE するのをやめて、「古い関連を DELETE する」+「新しい関連を INSERT する」の2操作に分解する。
これだけで、玉突き問題はきれいに消えます。しかも、テーブル定義は一切変えなくていい。アプリケーション層のロジック変更だけで完結します。
最初の玉突きケースを、この方針で書き直してみます。
BEGIN;
-- Phase 1: 影響を受ける現役の関連を DELETE
DELETE FROM project_leaders
WHERE project_id IN ('PJ-A', 'PJ-B', 'PJ-C');
-- Phase 2: 新しい関連を INSERT
INSERT INTO project_leaders (project_id, member_id)
VALUES
('PJ-A', 'B'),
('PJ-B', 'C'),
('PJ-C', 'A');
COMMIT;
Rails 風にも一行ずつ：
ActiveRecord::Base.transaction do
ProjectLeader.where(project_id: ['PJ-A', 'PJ-B', 'PJ-C']).delete_all
ProjectLeader.insert_all([
{ project_id: 'PJ-A', member_id: 'B' },
{ project_id: 'PJ-B', member_id: 'C' },
{ project_id: 'PJ-C', member_id: 'A' }
])
end
なぜこれで玉突き問題が消えるのか
Phase 1で、影響を受ける行をすべて削除します。この瞬間、対象の project_id を持つ行は1行も存在しなくなるので、UNIQUE 制約のスコープから完全に抜けます。
Phase 2は単なる INSERT です。新しい行を入れるだけなので、玉突きだろうが、相互入れ替えだろうが、N者間の円環サイクルだろうが、同じアルゴリズムで一律に処理できます。順列依存も消えます。
パターンA の良いところ
- テーブル定義を変えなくていい：アプリケーション層の修正だけで適用できる
- 既存システムへの適用が容易：マイグレーションも、データ移行も、ダウンタイムも不要
- 玉突き問題が構造的に消える：順列依存もなくなる
-
ON DUPLICATE KEY UPDATEへの依存が消える：衝突解決キーと更新対象が重なる構造を、コード側で発生させなくなる
履歴を残したい場合は？
ここまでは物理 DELETE で話を進めましたが、「過去に誰がリーダーだったか」を残したい要件もあるはずです。その場合は、次節の パターンB が自然な選択肢になります。
パターンB：もう一歩踏み込むなら、期間表現に移行する
パターンA で玉突き問題は解けるのですが、もう一歩踏み込んで「そもそも関連を時間情報として持つ」という選択肢もあります。期間表現方式への移行です。
この発想の出どころは、川島義隆氏の「イミュータブルデータモデル」 という設計思想です。
ざっくり言うと、
- データを リソース系（モノ、ライフサイクルを持つ実体）と イベント系（過去に起きた動かせない事実）に分類する
- イベントは UPDATE してはいけない。INSERT で積み上げ、終了は別の事実として記録する
- 非依存リレーションシップ（両エンティティが独立して存在できる関係）は、両者を独立したリソースとして残し、間に 交差エンティティ（多対多や1対多を解消する中間エンティティ）を置く
- 交差エンティティは、配属日時のような時間情報を属性に持てば イベント として読める
つまり、project_leaders のような中間テーブルは、関連自体を 「ある時点で始まり、ある時点で終わる事実」 として扱うのが本来の姿、ということになります。
これを project_leaders に当てはめると、設計はこうなります。
ポイントは以下です。
-
started_atとended_atで「いつから / いつまで」を持つ -
ended_at IS NULLの行が「現役のリーダー」を表す - 担当が変わるときは、既存行の
ended_atをセットする + 新しい行を INSERT する の2操作で表現する
パターンA と同じ「古い関連を閉じる + 新しい関連を INSERT」の構造ですが、
- いつ現役が終わったか、いつ始まったかが 時間情報として明示的に残る
- 監査要件・「いつ誰が担当だったか」系の問い合わせに直接答えられる
- 「現在状態」も「過去のある時点での状態」も、同じテーブルから引ける
といった追加のメリットが得られます。
A と B の選び方
判断軸は、「変更の履歴を残す必要があるか」 です。
- 履歴が不要 → パターンA：最新状態が分かれば足りる、過去の状態を参照したことがないなら、シンプルに物理 DELETE + INSERT で十分
- 履歴が必要 → パターンB：「いつ誰がリーダーだったか」を後から参照したい、監査要件で過去の状態を再構築したい、業務的に変更履歴が意味を持つなら、期間表現に投資する
判断軸はあくまで業務要件（履歴の必要性）です。「既存システムへの適用しやすさ」は副次的で、確かにパターンA の方が後付けしやすいですが、要件として履歴が必要なのに「移行コストが高いから A で済ます」とすると、別の問題（履歴が取れない、後から追加で苦しむ）が顕在化します。
実装ステップとしては、合わせ技も現実的です。
- 既存システムが玉突きバグで困っていて、履歴要件もある場合：まずパターンA で構造的バグを止血 → 並行してパターンB への移行を計画
- 履歴要件がない場合：パターンA で完了
属性は UPDATE していい。「関連」が危ない
ここまでの話を読んで「もう UPDATE 全部やめよう」となりそうですが、そう極端な話ではありません。重要なのは、属性の UPDATE と、関連の UPDATE は性質が違う ということです。
- ユーザーの メールアドレス や 表示名 のような、エンティティ単体の属性は、UPDATE で書き換えて問題ありません。最新値だけ持っていれば足りるケースがほとんどです。
- 一方、エンティティ間の 関連を表す中間テーブル の業務カラムは、構造上 UNIQUE 制約を多重に背負いやすく、業務カラムを UPDATE で書き換えると 衝突解決キーと更新対象の重なり を作り込みやすい。
なので、
エンティティの属性は UPDATE で表現していい。だが、エンティティ間の関連を表す中間テーブルの業務カラムは、UPDATE せず、DELETE + INSERT で表現する。
というのが、自分なりの設計指針です。これは1つの絶対則ではなく、「論理設計の段階ではこれを規律として通してから、物理設計で必要な妥協をする」というスタンスがちょうど良いと思っています。
少なくとも、エンティティ単体の属性更新よりも、関連の更新のほうが構造的に危険、という認識は持っておくと、設計レビューの解像度が上がります。
まとめ
冒頭の問い「中間テーブルに UPDATE は必要か？」に対する自分なりの答えは、
中間テーブルの「業務カラム」に対する UPDATE は必要ない。DELETE + INSERT の2操作だけで、関連の変更はすべて表現できる。
です。理由は、関連は本質的に「ある時点で始まり、ある時点で終わる事実」であって、現実世界には「関連を書き換える」という操作そのものが存在しないからです。それを UPDATE で表現しようとすると、今回のような構造的な矛盾を作り込みやすい。
整理すると、
- 中間テーブルの「関連の更新」を
ON DUPLICATE KEY UPDATEで実装すると、玉突きパターンで構造的に破綻することがある - 直接の原因は 衝突解決キーと更新対象カラムが UNIQUE 制約越しに重なっている こと
- もっと深い原因は、業務的に存在しない「関連の UPDATE」を UPDATE文で表現しようとしていること
- パターンA：UPDATE をやめて、DELETE + INSERT の2操作に分解する。アプリケーション層だけで完結、テーブル定義は触らない
-
パターンB：履歴が必要なら、
started_at/ended_atの期間表現に置き換える。履歴・監査が時間情報として自然に表現できる - A と B は、履歴を残すかどうか で選ぶ（既存システムへの当てはめやすさは副次的）
- 属性の UPDATE は OK。「関連」の UPDATE が危ない、というのが本質
「関連」を一段格上げして、独立した事実として扱う設計に切り替えると、玉突き問題が消えるだけでなく、履歴・監査・並行性の話まで一気に綺麗になります。中間テーブルの設計を見直すきっかけになれば嬉しいです。