deleted_atを使った論理削除を導入したあと、削除済みユーザーと同じ名前を登録できなくなることがあります。
原因は、削除後も行がテーブルへ残るためです。通常のUNIQUEインデックスから見ると、その名前はまだ使用中です。
CREATE UNIQUE INDEX idx_users_display_name
ON users(display_name);
たとえばTanakaを論理削除しても、同じ値を持つ新規行は重複として拒否されます。
一意性を有効な行だけに限定する
この問題は、deleted_at IS NULLを条件にした部分ユニークインデックスで解決できます。
今回はTanakaとtanakaも同じ名前として扱うため、lower()を使った式インデックスにします。
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_display_name_lower_live
ON users(lower(display_name))
WHERE deleted_at IS NULL;
このインデックスへ参加するのは、論理削除されていない行だけです。
削除済みのTanakaは制約から外れます。そのため、同じ名前を再登録できます。一方、有効なTanakaが残っている場合、tanakaの登録は拒否されます。
作成前に既存データを確認する
有効な重複行がすでに存在すると、インデックス作成は失敗します。
SELECT lower(display_name) AS normalized_name, count(*)
FROM users
WHERE deleted_at IS NULL
GROUP BY lower(display_name)
HAVING count(*) > 1;
重複が見つかった場合は、対象データを修正してからインデックスを作成します。
表記ゆれは保存前に正規化する
lower()だけでは、前後の空白やUnicode表現の違いまで吸収できません。表示名は保存前に正規化します。
export const normalizeDisplayName = (raw: string) =>
raw.normalize('NFKC').replace(/\s+/g, ' ').trim();
これにより、" Tanaka "と"Tanaka"のような差を除去できます。大小文字の一意性は、データベース側のlower()で保証します。
NFKCは全角英数や合字などを互換文字へ正規化します。識別子として望ましい規則か、事前に確認してください。
既存データが未正規化なら、インデックス作成前に同じ規則で一括変換し、発生した重複を解消する必要があります。
アプリケーションだけで重複判定を行うと、同時実行時に競合する可能性があります。最終的な保証はデータベースへ残します。
読み取り側も同じ規約へ揃える
論理削除対象の読み取りには、常にdeleted_at IS NULLを含めます。
export const liveCondition = (alias: string) => `${alias}.deleted_at IS NULL`;
LEFT JOINした関連データだけを除外したい場合、条件はWHEREではなくONへ置きます。
SELECT u.id, p.bio
FROM users AS u
LEFT JOIN user_profiles AS p
ON p.user_id = u.id
AND p.deleted_at IS NULL
WHERE u.deleted_at IS NULL;
関連テーブルの条件をWHEREへ移すと、LEFT JOINで親だけを残したいケースでも結果から除外される可能性があります。
pg-memでは部分インデックス外の行に注意する
pg-memでは、クエリ対象の行が部分インデックスの述語から外れる場合、不完全な結果を返す問題が報告されています。
たとえば、有効な外部アカウントIDだけを一意にするインデックスを作成します。
CREATE UNIQUE INDEX idx_users_external_account_live
ON users(external_account_id)
WHERE external_account_id IS NOT NULL
AND deleted_at IS NULL;
PostgreSQLでは、通常のUNIQUEインデックスでも複数のNULLを許可します。ここでIS NOT NULLを含める目的は、NULLの行をインデックス対象から外すことです。
次のデータは、インデックスの述語に含まれません。
INSERT INTO users (
display_name,
external_account_id,
deleted_at
)
VALUES ('Tanaka', NULL, NULL);
しかし、pg-mem 3.0.14では次のクエリが空配列を返すケースを再現しました。
SELECT *
FROM users
WHERE external_account_id IS NULL
AND deleted_at IS NULL;
同値な否定形へ書き換えると、該当行を取得できます。
SELECT *
FROM users
WHERE NOT (external_account_id IS NOT NULL)
AND deleted_at IS NULL;
この問題はissue #458でpg-mem 3.0.5を使って報告されています。回避策が機能する内部理由は確定していないため、実装上の保証として扱うべきではありません。
否定形への書き換えは、pg-memを使うテスト向けの互換対応です。本番SQLをエミュレーターへ合わせるより、重要な制約テストだけ実際のPostgreSQLやTestcontainersで実行する方が確実です。
まとめ
論理削除後に識別子を再利用させる場合、通常のUNIQUE制約では削除済み行まで対象になります。
- 有効な行だけを部分ユニークインデックスの対象にする
- 大小文字を同一視する場合は式インデックスを使う
- 空白やUnicodeの表記ゆれは保存前に正規化する
- 一意性の最終保証はデータベースへ置く
- インメモリDB固有の挙動を本番仕様へ持ち込まない
WHERE deleted_at IS NULLを一意性の定義へ含めることで、論理削除と再登録を両立できます。