背景
論理削除とユニーク制約の両立ができていないと、退会したユーザーが同じメールアドレスで再登録できないですよね。
この問題を解決するために、論理削除されたレコードを除外したユニーク制約の設定方法を紹介します。
Generated Columnを利用した両立方法
論理削除とユニーク制約を両立させるための解決策として、Generated Columnを利用します。
Generated Columnを用いることで、論理削除されたレコードを除外したユニーク制約を設定することが可能です。
Generated Columnとは
「Generated Column」は、データベース内の他のカラムの値に基づいて動的に計算されるカラムです。生成カラムには以下の2種類があります。
- Virtual ： 値がデータベースに保存されず、参照時に動的に計算されるカラム。クエリ実行時に必要なタイミングで計算されるため、ストレージを節約することができる。しかし、クエリのたびに計算が発生するため、頻繁に使用される場合はパフォーマンスに影響を与えることがある。
- Stored： 値がデータベースに保存され、挿入または更新時に計算されるカラム。値が一度計算されて保存されるため、後のクエリでは計算が不要となり、パフォーマンスが向上する。ただし、ストレージの使用量が増える可能性がある。
論理削除とユニーク制約を両立させたいテーブルにGenerated Columnを追加し、ユニーク制約を適用するカラムとの複合ユニーク制約を設定します、
以下のコードではGenerated Columnで追加したカラム名をexistとしています。
下記のコードはLaravelのマイグレーションファイルです。
Schema::table('users', function (Blueprint $table) {
$table->unsignedTinyInteger('exist')->virtualAs('IF(ISNULL(deleted_at), 1, NULL)')->nullable();
$table->unique(['email', 'exist']);
});
Generated Columnで追加したexistカラムは、deleted_atがNULL（論理削除されていない場合）なら1を返し、deleted_atに値があれば（論理削除されている場合）はNULLを返します。
MySQLの複合ユニーク制約では、設定しているカラムのいずれかがNULLの場合はユニーク制約のチェックから除外されるという性質があります。
したがって、論理削除されたレコード（existがNULLのレコード）はユニーク制約のチェックから除外されるため、同じemailを持つ新しいレコードを挿入することが可能になるわけです。
そのためemailとexistの複合ユニーク制約の設定により、論理削除されたレコードを除外したユニーク制約となります。
最後に
MySQLで論理削除を行うテーブルにユニーク制約を設定する際は、くれぐれも両立のし忘れにご注意ください。
参考文献