MySQLで論理削除とユニーク制約を両立させる：生成列（generated column）の活用

はじめに

アプリケーションでデータを削除する際、物理削除ではなく「論理削除」を使うケースは非常に多いです。
論理削除では deleted_at カラムに削除日時を入れることで、レコードを残したまま削除扱いにできます。

しかしここで問題になるのが、ユニーク制約の扱いです。
例えばメールアドレスにユニーク制約がある場合、論理削除済みのレコードにも制約が効いてしまい、同じメールアドレスを再登録できなくなります。

私自身も、論理削除を採用するとユニーク制約を付けるのが難しくなることは把握していたつもりですが、実際にテーブル設計をしてみると思ったよりも悩まされました。

そこで今回の記事では、MySQL で未削除のレコードだけをユニークにする方法を紹介します。
ポイントは、MySQL の 生成列（generated column） を利用することです。

解説

MySQL では部分ユニーク制約を付けられない

理想的には「deleted_at が NULL の場合だけユニーク制約を効かせたい」というケースがあります。
PostgreSQL などでは「A列が〇〇という条件の場合のみ、B列がユニークかチェックする」という部分ユニーク制約が使えますが、残念ながら MySQL にはこの機能はありません。

そのため、単純にユニーク制約を付けても、論理削除済みのレコードは制約の対象になってしまい、意図した動作にはなりません。

解決方法：生成列を使い、擬似的に削除されていないときだけ比較対象にする

そこで使えるのが生成列（generated column）です。

例として、ユーザー情報(users)にメールアドレス(email)を持っている場合を考えます。

未削除レコード (deleted_at IS NULL) の場合だけ email を返し、削除されている場合 (deleted_at IS NOT NULL) は NULL を返すような生成列を作ります。

ALTER TABLE users
ADD COLUMN email_for_unique VARCHAR(255)
    AS (CASE WHEN deleted_at IS NULL THEN email ELSE NULL END) VIRTUAL,
ADD UNIQUE KEY unique_email(email_for_unique);

これにより、

• 未削除レコード → email_for_unique は email が返される → ユニーク制約が有効
• 削除済みレコード → email_for_unique は NULL が返される → 重複してもOK

という動作になります。

なお、これは『MySQL ではユニーク制約のカラムに NULL が含まれる場合、NULL 同士は「等しい」とは見なされない』という仕様により実現されます。

やってみた

実際に動作を試してみます。

テーブル作成

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    deleted_at DATETIME DEFAULT NULL,
    email_for_unique VARCHAR(255)
        AS (CASE WHEN deleted_at IS NULL THEN email ELSE NULL END) VIRTUAL,
    UNIQUE KEY unique_email (email_for_unique)
);

データ登録してみる

-- 新規登録
INSERT INTO users (email) VALUES ('alice@example.com'); -- OK

-- 同じメールをもう1件（未削除同士）
INSERT INTO users (email) VALUES ('alice@example.com'); -- NG（ユニーク制約）
-- SQL Error [1062] [23000]: Duplicate entry 'alice@example.com' for key 'users.unique_email'

-- 論理削除
UPDATE users SET deleted_at = NOW() WHERE email = 'alice@example.com';

-- 同じメールで再登録
INSERT INTO users (email) VALUES ('alice@example.com'); -- OK（削除済みは無視される）

期待どおり、未削除レコードに対してはユニーク制約が働き、削除済みレコードに対しては再登録が可能になりました。

まとめ

• MySQL では部分ユニーク制約を付けられないため、単純にユニーク制約を付けるだけでは論理削除との両立はできない
• 生成列（generated column）を利用すると、「未削除レコードだけユニーク」を自然に実現できる
• アプリ側に特別なロジックを入れなくても、DB側で整合性を保つことができる

論理削除を使うシステムでは非常に便利なパターンなので、ぜひ活用してみてください。