💾
論理削除でデータを管理しているテーブルでユニーク制約を設定する方法

2024/12/01に公開
 はじめに先日、論理削除でデータを管理しているテーブルにユニーク制約を設定する機会がありました。このような状況で重複を許容しないカラム（今回の例ではname）にのみユニーク制約を設定してしまうと、論理削除済みのレコードに重複するnameが存在する場合に挿入・更新ができなくなります。今回のような場合にどのようにユニーク制約を設定したか、前提知識も含めてご紹介したいと思います。

 前置き「はじめに」の状況を簡単にご説明します。以下の「name」にユニーク制約が設定されている場合は、Aはid = 1とnameが重複するため登録することができません。これについてはユニーク制約が機能しているだけなので特に問題はありません。

一方で論理削除ではレコードがDELETEされるわけではありません。そのため、Bは論理削除されたid = 2とnameが重複するため登録できません。実際のアプリで考えると論理削除済みのユーザーとnameが重複するので、同じnameで再登録できない...といった状況になります。今回はこれをどのように回避したかという話になります。


 まずは結論から前提知識の説明から入ると長くなるのでまずは結論からです。今回はGenerated Columnsと複合ユニーク制約を使用しました。

「is_unique_target」をGenerated Columnsとして定義し、値を計算するための式にIF(deleted_at IS NULL, 1, NULL)を指定しています（= 論理削除されていないレコードはis_unique_targetが1になります）。複合ユニーク制約については以下のイメージの「name」「is_unique_target」に設定しており、レコードの挿入・更新時には2つの組み合わせがユニークかどうかで判断されます。

これで論理削除済みのレコードに重複するnameが存在していても、挿入・更新時には無視されるようになります。上記のテーブル定義をLaravelのmigrationで書くと以下のようになります。$table->softDeletes();は「deleted_at」のことです。
public function up(): void
{
    Schema::create('sample_table', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->boolean('is_unique_target')->virtualAs('IF(deleted_at IS NULL, 1, NULL)');
        $table->softDeletes();

        $table->unique(['name', 'is_unique_target']);
    });
}

 前提知識ここからは前提知識になります。結論だけでは説明が不十分な場合は目を通していただければと思います。ざっくりとした説明しか記載しておりませんので、詳しく知りたい方や厳密には違う気がするなと思う方は単語だけ覚えてもらって各々で調べていただけると幸いです。

 論理削除とはSQLのDELETEで削除する方法を「物理削除」と呼びます。一方で「deleted_at（削除日時）」や「is_deleted（削除済みか）」などのカラムに値が入っている状態を削除済みとして扱う方法が「論理削除」となります。Laravelなどで使われるdeleted_atでは、デフォルト値はnullとなっており削除済みの場合には削除日時が入ります。論理削除の使用には賛否両論ありますが、実際にレコードをDELETEするわけではないので（論理）削除済みのデータをすぐに元に戻せるといった良さがあります。私の所属しているプロジェクトではよく使われています。

 複合ユニーク制約複数のカラムの組み合わせがユニークかどうかで判断する制約になります。今回の場合で言うと「name」と「is_unique_target」の組み合わせがユニークかどうかで判断しています。

ここで「『name』と『deleted_at』の組み合わせでも実現できるのでは？」と思われた方もいるかもしれません。ですが、今回使用したMySQLではnull同士は重複していると判断されません。そのため「name」が重複していても「deleted_at」がnullだとユニークなレコードが挿入・更新されたと判断されてしまいます。そこで「deleted_at」がnullの場合に値が1となる「is_unique_target」を定義しています。

逆に言えば「is_unique_target」は削除済みの場合にnullとなるので「name =（論理削除済みの名前）」+「is_unique_target = null」の論理削除済みのデータをいくらでも保持することができます。ユーザーを何度も削除する機会があるかはわかりませんが、論理削除するたびにユニーク制約でエラーが発生することもないので安心ですね。

 Generated Columnsこれまでの説明を踏まえて「初めから『is_unique_target』をカラムとして定義して、複合ユニーク制約を設定すれば良いのでは？」と思われた方もいるかもしれません。私も最初はそのように考えていました。ですが、今回のことを実現するためにわざわざ論理削除を2つのカラムで二重管理するのも微妙だな...という悩みがありました。そんな悩みを解決してくれたのがGenerated Columnsです。

Generated Columnsではテーブル定義（今回の場合はmigration）で定義した式に従って値を計算してくれます。こちらを利用すると「deleted_at」の更新と連動して「is_unique_target」も更新されることになり、ユーザー側で意識して二重管理する必要がなくなります。

また、Generated ColumnsにはVIRTUALまたはSTOREDを指定することができます。VIRTUALではSELECTを実行した際に値が計算され、計算結果は保存されません。STOREDでは挿入・更新の際に値が計算され、計算結果が保存されます。ただし、VIRTUALでも計算後の値にindexを貼れるため今回はこちらを採用しました。

 まとめ今回は論理削除でデータを管理しているテーブルにユニーク制約を設定しました。複合ユニーク制約とGenerated Columnsを利用することで、論理削除されていないデータにのみ重複しているかどうかの判断が行われるようになりました。個人的には良い落とし所を見つけたと思いますが、より良い方法がないか日々模索していこうと思います。
はじめに

前置き

まずは結論から

前提知識

論理削除とは

複合ユニーク制約

Generated Columns

まとめ

Discussion
