論理削除でも一意性制約を正しく機能させるDB別実装パターン

論理削除を採用しているシステムで、「削除済みユーザーと同じメールアドレスが使えない」という問題に遭遇したことはありませんか？

この記事では、各DB製品での解決方法を紹介します。

問題の背景

論理削除では、レコードを物理的に削除せず「削除フラグ」を立てるだけです。しかし、メールアドレスなどに一意性制約をかけると、削除済みレコードと重複してしまい新規登録ができなくなります。

NULLと一意性制約の重要な性質

解決方法を理解するために、まずNULLの性質を押さえておきましょう。

UNIQUE制約においてNULLは特別扱いされます：

• NULL ≠ NULL（NULLはNULLと等しくない）
• PostgreSQL・MySQLのUNIQUE制約では、NULL同士は重複とみなされない（複数のNULLを許容）
• OracleのB-treeインデックスは、すべてのキー列がNULLの行を含めない

この性質を利用して、有効なレコードのみを一意性チェックの対象にできます。

解決方法

PostgreSQL：部分インデックス（推奨）

PostgreSQLでは、特定の条件を満たす行だけを対象にしたインデックスを作成できます。

CREATE UNIQUE INDEX users_email_unique
ON users (email)
WHERE deleted_at IS NULL;

deleted_at IS NULL（未削除）のレコードだけが一意性チェックの対象になります。最もシンプルで意図が明確な方法です。

補足: PostgreSQL 15以降では NULLS NOT DISTINCT オプションが追加され、NULLを等しいとみなすこともできます（デフォルトは NULLS DISTINCT）。

SQL Server：フィルター付きインデックス（推奨）

SQL Serverでも同様に、条件付きのユニークインデックスが使えます。

CREATE UNIQUE INDEX users_email_unique
ON users (email)
WHERE deleted_at IS NULL;

注意: SQL ServerはNULLを「値」として扱うため、通常のユニーク制約では複数のNULLを許可しません。フィルター付きインデックスを使う必要があります。

MySQL：2つのアプローチ

MySQLには部分インデックス機能がありませんが、以下の2つの方法があります。

方法1：関数インデックス（MySQL 8.0.13以降・推奨）

MySQL 8.0.13以降では関数インデックスが使えます。UNIQUE制約でNULL同士が重複とみなされない特性を利用します。

CREATE UNIQUE INDEX users_email_unique
ON users ((CASE WHEN deleted_at IS NULL THEN email ELSE NULL END));

• 未削除レコード：emailがインデックスに含まれる（一意性チェック対象）
• 削除済みレコード：NULLが返される（NULL同士は重複とみなされないため、複数の削除済みレコードが許容される）

注意: クエリで一意性エラーを発生させるには、INSERT時に同じ式を評価する必要があります。

方法2：有効フラグ＋複合UNIQUE（互換性重視）

MySQL 5.7以前との互換性が必要な場合は、この方法を使います。

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    active_flag INT NULL,
    CONSTRAINT active_check CHECK (active_flag = 0),
    CONSTRAINT users_email_unique UNIQUE (email, active_flag)
);

注意: CHECK制約はMySQL 8.0.16以降でのみ機能します。

Oracle：2つのアプローチ

Oracleも同様に2つの方法があります。

方法1：関数インデックス（推奨）

OracleのB-treeインデックスはNULLを含まない特性を利用します。テーブル構造を変更せずに済むため、こちらが推奨されます。

CREATE UNIQUE INDEX users_email_unique
ON users (CASE WHEN deleted_at IS NULL THEN email ELSE NULL END);

方法2：有効フラグ＋複合UNIQUE

MySQLと同様のアプローチも使えます。

CREATE TABLE users (
    id NUMBER NOT NULL PRIMARY KEY,
    email VARCHAR2(255) NOT NULL,
    active_flag NUMBER(1) NULL,
    CONSTRAINT active_check CHECK (active_flag = 0),
    CONSTRAINT users_email_unique UNIQUE (email, active_flag)
);

避けるべきパターン

deleted_atとの複合UNIQUE

以下の方法は多くのDB製品で意図通りに動作しません：

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT users_email_unique UNIQUE (email, deleted_at)
);

DB製品ごとの挙動の違い：

SQL Serverでは動作するが、推奨されない理由：

• インデックス効率: 削除済みレコードもインデックスに含まれるため、サイズが大きくなる
• 意図の不明確さ: NULLの特殊な挙動に暗黙的に依存している
• クロスプラットフォーム互換性: 他のDB製品では動作しないため、設計パターンを統一できない
• エッジケース: 同一タイムスタンプで複数削除された場合に重複エラーが発生する可能性

これらの理由から、SQL Serverでもフィルター付きインデックスの使用を推奨します。

DB製品別の特徴まとめ

参考情報

• PostgreSQL: Partial Indexes
• SQL Server: Create Filtered Indexes
• MySQL: CREATE INDEX Statement
• Oracle: Function-Based Indexes