論理削除と再登録のベストプラクティス（MySQL / PostgreSQL）

目次

1. 問題の整理
2. よくあるアンチパターン
3. PostgreSQL：部分インデックス（Partial Index）が最適解
4. MySQL：部分インデックスがないので代替手段を使う
5. 再登録の設計方針：「新しい行」か「復活」か
6. 運用面のベストプラクティス
7. まとめ

1. 問題の整理

論理削除（deleted_at や is_deleted で「削除済み」にする方式）では、行が物理的に残るため 一意制約と衝突 する。

users
| id | email           | deleted_at          |
|----|-----------------|---------------------|
| 1  | taro@example.com| 2026-01-10 12:00:00 |  ← 退会済み
| 2  | taro@example.com| NULL                |  ← 再登録したい → UNIQUE(email) 違反

求められる制約は次のとおり。

• 有効な行（未削除）の中では email は一意
• 削除済みの行 は何件あってもよい

これを DB の制約で保証するのが基本方針になる。アプリ側のチェック（SELECT してから INSERT）だけでは、同時リクエストで重複が入るため不十分。

2. よくあるアンチパターン

❌ UNIQUE(email, deleted_at) で済ませる

-- 一見よさそうだが壊れている
ALTER TABLE users ADD UNIQUE (email, deleted_at);

SQL の一意制約では NULL 同士は等しくない と扱われるため、deleted_at IS NULL の行（有効な行）が 何件でも重複登録できてしまう。MySQL・PostgreSQL（デフォルト）ともに同じ挙動。

❌ 一意制約を外してアプリでチェックする

競合状態（レースコンディション）で重複が発生する。

❌ 削除時に email を書き換える（taro@example.com#deleted_1）

動作はするが、元の値が汚れる・長さ制限に引っかかる・監査や復元が面倒になる。他の手段がない場合の最終手段。

3. PostgreSQL：部分インデックス（Partial Index）が最適解

PostgreSQL は WHERE 句付きのインデックスをサポートしており、条件に合う行だけに一意制約をかけられる。

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text        NOT NULL,
  deleted_at timestamptz NULL
);
 
CREATE UNIQUE INDEX users_email_active_uniq
  ON users (email)
  WHERE deleted_at IS NULL;

• 有効な行同士は email 重複不可
• 削除済みの行は対象外なので、何件残っていても再登録できる
• インデックスサイズも有効行の分だけになり小さい

大文字小文字を区別しない場合

CREATE UNIQUE INDEX users_email_active_uniq
  ON users (lower(email))
  WHERE deleted_at IS NULL;
-- または citext 型を使う

注意点

① クエリの条件をインデックスの条件と合わせる

プランナーが部分インデックスを使うのは、クエリの WHERE がインデックスの条件を含む場合だけ。

-- 部分インデックスが使われる
SELECT * FROM users WHERE email = $1 AND deleted_at IS NULL;
 
-- 使われない（削除済みも含めて探すため）
SELECT * FROM users WHERE email = $1;

② ON CONFLICT では条件も指定する

INSERT INTO users (email) VALUES ($1)
ON CONFLICT (email) WHERE deleted_at IS NULL
DO NOTHING;

WHERE deleted_at IS NULL を省くと、対応する一意インデックスが見つからずエラーになる。

③ 部分インデックスは UNIQUE 制約（CONSTRAINT）ではなくインデックス

外部キーの参照先にはできない。外部キーで参照したい場合は、主キー（id）を参照する設計にする。

④ NULLS NOT DISTINCT（PostgreSQL 15+）について

UNIQUE NULLS NOT DISTINCT (email, deleted_at) とすると NULL 同士も等しいとみなされ、有効行の重複は防げる。ただし削除済みの行同士で deleted_at が同じ値だと衝突するので、論理削除の用途では部分インデックスのほうが素直。

4. MySQL：部分インデックスがないので代替手段を使う

MySQL（8.x 系）は部分インデックスをサポートしていない。代わりに 「有効な行では値を持ち、削除済みでは NULL になる列」 を作り、そこに一意制約をかける。一意インデックスは NULL を複数許容するので、削除済みの行は衝突しない。

方法A：生成列（Generated Column）＋ UNIQUE【推奨】

CREATE TABLE users (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email        VARCHAR(255) NOT NULL,
  deleted_at   DATETIME(6)  NULL,
  active_email VARCHAR(255)
    AS (IF(deleted_at IS NULL, email, NULL)) STORED,
  UNIQUE KEY users_active_email_uniq (active_email)
);

• 有効な行 → active_email = email → 重複不可
• 削除済み → active_email = NULL → 何件でも可
• PostgreSQL の部分インデックスとほぼ同じ意味になる
• VIRTUAL でもインデックスは張れるが、扱いやすさから STORED を選ぶことが多い

複合キーの場合（例：テナント内で一意）：

active_flag TINYINT AS (IF(deleted_at IS NULL, 1, NULL)) STORED,
UNIQUE KEY (tenant_id, email, active_flag)

active_flag が NULL の行は一意性チェックから外れる。

方法B：関数インデックス（MySQL 8.0.13+）

CREATE UNIQUE INDEX users_active_email_uniq
  ON users ((IF(deleted_at IS NULL, email, NULL)));

内部的には隠し仮想列を作るのと同じ。列定義が増えないのが利点だが、ORM のマイグレーションツールやスキーマ差分ツールで扱いにくいことがあるため、チームでは方法A のほうが分かりやすい。

方法C：is_active 列を 1 / NULL で持つ

is_active TINYINT NULL DEFAULT 1,  -- 有効=1, 削除=NULL
UNIQUE KEY (email, is_active)

生成列が使えない環境（古い MySQL など）向け。0 ではなく NULL にするのがポイント。アプリ側で deleted_at と is_active を必ず一緒に更新する必要がある。

方法D：番兵値（sentinel）を使う

deleted_at DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00',  -- 未削除
UNIQUE KEY (email, deleted_at)

未削除を NULL ではなく固定値にすることで、アンチパターンの UNIQUE(email, deleted_at) が正しく動くようになる。ただし「NULL = 未削除」という一般的な慣習から外れ、ORM の論理削除機能と相性が悪いので、新規設計ではおすすめしない。

MySQL の注意点

• 生成列の式は決定的（deterministic）である必要がある。IF() は問題ない
• INSERT ... ON DUPLICATE KEY UPDATE は生成列の一意キーでも発火する
• 有効行の検索用には別途 INDEX (email) か INDEX (active_email) を用意し、クエリでは WHERE active_email = ? を使うとインデックスが効きやすい

5. 再登録の設計方針：「新しい行」か「復活」か

基本は 新しい行を作る ほうが安全。復活方式は、古い関連データ（過去の権限、設定、個人情報）が意図せず戻る危険がある。

復活方式の書き方

PostgreSQL

-- 削除済みの行があれば復活、なければ新規
WITH revived AS (
  UPDATE user_tags
     SET deleted_at = NULL
   WHERE user_id = $1 AND tag_id = $2 AND deleted_at IS NOT NULL
   RETURNING *
)
INSERT INTO user_tags (user_id, tag_id)
SELECT $1, $2
WHERE NOT EXISTS (SELECT 1 FROM revived)
ON CONFLICT (user_id, tag_id) WHERE deleted_at IS NULL DO NOTHING;

削除済みの行が複数ある可能性があるなら、復活対象を最新の1件に絞る。中間テーブルで復活方式しか使わないなら、そもそも 通常の UNIQUE(user_id, tag_id) にして削除済み行が1件しか存在しないようにする ほうが単純。

MySQL

-- 中間テーブルで通常の UNIQUE(user_id, tag_id) を張っている場合
INSERT INTO user_tags (user_id, tag_id, deleted_at)
VALUES (?, ?, NULL)
ON DUPLICATE KEY UPDATE deleted_at = NULL;

削除済みのものを復元するときの衝突

「ユーザーAを退会 → 同じ email で新規登録 → 管理者が旧Aを復元」という操作は一意制約違反になる。復元処理では衝突をエラーとして扱い、画面で「同じメールアドレスの有効なアカウントが存在します」と返すようにしておく。

6. 運用面のベストプラクティス

検索漏れを防ぐ

論理削除で一番多い事故は WHERE deleted_at IS NULL の書き忘れ。

• ORM の論理削除機能を使う（Laravel SoftDeletes、Rails discard / paranoia、Hibernate @SQLDelete + @SQLRestriction、Django は django-safedelete など）
• または有効行だけのビューを作り、アプリはビューを参照する

CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

外部キーと論理削除

• DB の ON DELETE CASCADE は論理削除では動かない。子テーブルの論理削除はアプリ側（またはトリガー）で行う
• 削除済みの親を参照する子が残ることを前提にクエリを書く

deleted_at を使う（is_deleted より）

• 削除日時が分かる
• deleted_at IS NULL で有効判定できる
• 「誰が削除したか」が必要なら deleted_by も追加

個人情報・データ量の扱い

• 論理削除は「消したことにする」だけで、データは残る。退会者の個人情報削除が求められる場合（GDPR、個人情報保護法の対応方針など）は、一定期間後に 匿名化または物理削除するバッチ を用意する
• 削除済みの行が大量に溜まるテーブルは、アーカイブテーブルへ移動 する方式も検討する（本テーブルは物理削除し、通常の UNIQUE(email) が使えるようになる）

論理削除が本当に必要か見直す

「念のため論理削除」は複雑さの元になる。目的が監査・履歴だけなら、次のほうが単純なことが多い。

• 本テーブルは物理削除し、削除前の行を users_deleted / 監査ログに保存する
• PostgreSQL ならトリガーで履歴テーブルに書き出す

7. まとめ

• 一意性は 必ず DB の制約で保証 する
• 「有効行だけ一意」を表現する：PostgreSQL は部分インデックス、MySQL は NULL になる生成列
• 再登録は原則 新しい行、中間テーブルは 復活 も選択肢
• ORM やビューで deleted_at IS NULL の書き忘れを防ぐ
• 個人情報と肥大化は、匿名化・アーカイブで別途対処する