データモデル設計ガイド

論理削除 vs 物理削除 — deleted_at パターンの落とし穴

論理削除と物理削除のトレードオフ、UNIQUE 衝突を部分インデックスで解消する定石。

論理削除 (soft delete) とは

論理削除は、行を物理的に DELETE せず、deleted_at TIMESTAMPTZ NULL などのフラグ列に削除時刻を記録する方式です。アプリ側のクエリはすべて WHERE deleted_at IS NULL を付けて「生存中の行だけ」を見るようにします。

利点は 復元可能・監査ログが残る・関連データが壊れないこと。欠点はすべてのクエリに条件追加が必要・テーブルが肥大化・UNIQUE 制約が機能しなくなること。GDPR 等の「削除権 (right to erasure)」要件とは相性が悪い点にも注意。

sql

ALTER TABLE users
  ADD COLUMN deleted_at TIMESTAMPTZ NULL;

-- 生存中の行のみを返すクエリ
SELECT * FROM users WHERE deleted_at IS NULL;

UNIQUE 制約との衝突

論理削除の最大の罠は UNIQUE 制約です。email UNIQUE なテーブルで、ユーザー A が退会（論理削除）した後、同じメールアドレスで再登録できません。論理削除行がまだテーブルに残っていて UNIQUE 違反になるからです。

解決策:

部分インデックス (partial index): 生存中の行にだけ UNIQUE を適用（PostgreSQL / SQLite / SQL Server）
生成列 + UNIQUE: deleted_at IS NULL のときだけ email を保持する生成列を作り、そこに UNIQUE を張る（MySQL）
物理削除に変える: UNIQUE 項目を扱う業務では、そもそも論理削除をやめる選択肢

sql

-- PostgreSQL: 生存中の email だけ UNIQUE
CREATE UNIQUE INDEX users_email_unique
  ON users(email)
  WHERE deleted_at IS NULL;

-- MySQL: 生成列で代用
ALTER TABLE users
  ADD email_active VARCHAR(255)
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) STORED,
  ADD UNIQUE (email_active);

外部キーと論理削除

外部キー制約は論理削除を認識しません。親が論理削除されても、子から見れば親は「存在する」ので FK は通ります。これは意図通りとも言えるしバグとも言える難所です。

アプリ側の対応方針:

JOIN 時に parent.deleted_at IS NULL を常に付けるルールにする（ORM の共通スコープに仕込む）
参照先が論理削除済みの行を「参照だけ許可・新規参照は禁止」と業務的に定義する
親を論理削除する際は子も論理削除する（アプリ or トリガで実装。CASCADE 相当）

いつ物理削除を選ぶか

論理削除はデフォルトではありません。次のケースでは物理削除の方が適切です。

GDPR / 個人情報保護法の削除権: 「消してほしい」と言われたら本当に消す必要がある
一時的なセッション / キャッシュデータ: 復元する意味がない
高スループットのログ・キュー: 肥大化を避けたい
UNIQUE 制約の多いマスタ: 部分インデックス等の回避策が複雑化する

折衷案として、論理削除で N 日保持 → バッチで物理削除のパイプラインはよく使われます。復元要望への対応とテーブル肥大化の両方を解決できます。

RDBMS 差分と実装 Tips

部分インデックスのサポート状況:

PostgreSQL / SQLite / SQL Server: CREATE INDEX ... WHERE で部分インデックスをネイティブサポート
MySQL: 部分インデックス (行のフィルタ) はサポートされず、生成列 + UNIQUE で代用

さらに、オプティマイザが deleted_at IS NULL のスキップスキャンを活用できないケースがあるため、大規模テーブルでは (deleted_at, tenant_id) など複合インデックスを設計するか、そもそも生存行だけの部分インデックスを作る設計が有効です。