こんにちは、タクマです。
今回はLaravelで論理削除とユニーク制約を両立させる方法を解説します。
前提条件
Laravelバージョン:8系(筆者の環境では8系ですが、8以下でも大丈夫かと思います。)
DB: MySQL( MySQL以外のDBでは同じ挙動にならない可能性が高いです。)
デフォルトでは論理削除とユニーク制約の両立はできていない
検証のため、以下のようなusersテーブルを作成します。
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name')->nullable();
$table->string('email')->unique(); // ユニーク制約
$table->string('password');
$table->timestamps();
$table->softDeletes(); // 論理削除
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}
上記マイグレーションファイルをマイグレートすると以下のようなテーブルになります。
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
id | bigint unsigned | NO | PRI | auto_increment | |
name | varchar(191) | YES | |||
varchar(191) | NO | UNI | |||
password | varchar(191) | NO | |||
created_at | timestamp | YES | |||
updated_at | timestamp | YES | |||
deleted_at | timestamp | YES |
emailはユニーク制約がついています。
usersテーブルに論理削除済のレコードを一件作成します。
INSERT INTO users (name, email, password, created_at, updated_at, deleted_at)
VALUES ('田中太郎', 'tanaka@example.com', SHA2('password', 256), NOW(), NOW(), NOW());
作成後、以下のようなレコードができています。
SELECT * FROM users;
id | name | password | created_at | updated_at | deleted_at | |
---|---|---|---|---|---|---|
1 | 田中太郎 | tanaka@example.com | 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8 | 2022-09-10 21:02:18 | 2022-09-10 21:02:18 | 2022-09-10 21:02:18 |
この論理削除済レコードとemailが同じレコードを作成します。
INSERT INTO users (name, email, password, created_at, updated_at)
VALUES ('田中二郎', 'tanaka@example.com', SHA2('password', 256), NOW(), NOW());
すると、下記のように論理削除済のレコードとemailが重複しエラーが起こります。
SQLエラー [1062] [23000]: Duplicate entry 'tanaka@example.com' for key 'users.users_email_unique'
論理削除のユニーク制約を両立させる対応をしていない場合、このように論理削除済レコードとの重複エラーが発生してしまうため、両立させる対応が必要になります。
論理削除とユニーク制約を両立させる方法
結論から言うと、論理削除とユニーク制約を両立させるには、論理削除済の場合に値がnullになるカラムとユニーク制約を設定するカラムとの複合ユニーク制約を設定すればOKです。
なぜなら、MySQLのユニーク制約はnullを対象外にするからです。
つまり複合ユニーク制約の対象となるカラムのうち一つでもnullであればそのレコードはユニーク制約の対象外となります。
MySQLの「ユニーク制約はnullを対象外にする」という性質を利用し、論理削除済の場合に値がnullになるカラムとユニーク制約を設定するカラムとの複合ユニーク制約を設定することで論理削除とユニーク制約を両立させることができます。
Generated Columnを利用して論理削除済の場合に値がnullになるexistカラムを追加する
論理削除済の場合に値がnullになり、未削除の場合は1が入るexist
というカラムを追加します。
このexist
カラムを追加する際に登場するのがGenerated Columnです。
Generated Columnとは?
Generated Columnは、定義した式に従って値を生成してカラムのように扱える仕組みです。Generate Columnには種類が2種類あり、VIRTUALとSTOREDになります。何も指定を行わなかった場合はVIRTUALがデフォルトで適用されます。
VIRTUALは計算結果を保存せずにSELECTするたびに再計算される仕組みで、文字通り仮想的なカラムとして操作することができます。
対してSTOREDは、挿入と更新を行った際に事前に計算した結果をカラムに保存する仕組みになります。こちらは物理的に結果が保存されているので、通常のカラムと同様に扱うことができます。
gihyo.jp Generated Columnを利用してみる
とても分かりやすい解説でしたので、引用させていただきました。
このGenerated Column(VIRTUAL)を利用して、論理削除された場合にnullが、未削除の場合は1が入るカラムを追加します。
existカラムを追加する
exist
カラムを追加するマイグレーションファイルを作成します。
php artisan make:migration add_column_exist_to_users_table
作成されたマイグレーションファイルを以下のように修正します。
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class AddColumnExistToUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::table('users', function (Blueprint $table) {
// Generated Column(VIRTUAL)を利用して、
// deleted_atがnullの時に1が、そうでない場合にnullが自動的に入るexistカラムを追加
$table->unsignedTinyInteger('exist')->nullable()->virtualAs('IF(ISNULL(deleted_at), 1, NULL)');
// emailのユニーク制約を削除
$table->dropUnique('users_email_unique');
// email × existの複合ユニーク制約を設定
$table->unique(['email', 'exist'], 'users_email_exist_unique');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::table('users', function (Blueprint $table) {
$table->dropUnique('users_email_exist_unique');
$table->unique('email', 'users_email_unique');
$table->dropColumn('exist');
});
}
}
上のマイグレーションファイルをマイグレートします。
php artisan migrate
usersテーブルの状態を確認します。
SHOW CREATE TABLE users;
|Table|Create Table|
|-----|------------|
|users|CREATE TABLE `users` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`password` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`deleted_at` timestamp NULL DEFAULT NULL,
`exist` tinyint unsigned GENERATED ALWAYS AS (if((`deleted_at` is null),1,NULL)) VIRTUAL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_exist_unique` (`email`,`exist`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci|
exist
カラムが追加されており、email
× exist
の複合ユニーク制約も設定されています。
usersテーブルのデータを見てみましょう。
SELECT * FROM users;
id | name | password | created_at | updated_at | deleted_at | exist | |
---|---|---|---|---|---|---|---|
1 | 田中太郎 | tanaka@example.com | 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8 | 2022-09-11 10:29:21 | 2022-09-11 10:29:21 | 2022-09-11 10:29:21 |
先ほど作成した論理削除済のレコードのexist
カラムにはnullが入っています。
論理削除済レコードとemailが重複してもエラーが起こらないことを確認
では先ほどemailの重複エラーが起きたINSERT文を再度実行してみましょう。
INSERT INTO users (name, email, password, created_at, updated_at)
VALUES ('田中二郎', 'tanaka@example.com', SHA2('password', 256), NOW(), NOW());
重複エラーが起こらず、論理削除済レコードとemailが重複するレコードが作成できました。
// 結果
Updated Rows 1
Query INSERT INTO users (name, email, password, created_at, updated_at)
VALUES ('田中二郎', 'tanaka@example.com', SHA2('password', 256), NOW(), NOW())
Finish time Sun Sep 11 10:38:52 JST 2022
usersテーブルのレコードを確認します。
SELECT * FROM users;
id | name | password | created_at | updated_at | deleted_at | exist | |
---|---|---|---|---|---|---|---|
1 | 田中太郎 | tanaka@example.com | 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8 | 2022-09-11 10:29:21 | 2022-09-11 10:29:21 | 2022-09-11 10:29:21 | |
2 | 田中二郎 | tanaka@example.com | 5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8 | 2022-09-11 10:38:52 | 2022-09-11 10:38:52 | 1 |
未削除のID2のレコードは、existカラムに1が入っています。
これで論理削除とユニーク制約の両立ができました!
このようにGenerated Columnを利用すると定義した式(deleted_atがnullだったらexistには1が入り、そうでない場合はnullが入る)に従って自動的に値が入るので、ソースコードでexistに値を保存する処理などの記述は不要です。
未削除のレコードとemailが重複する場合は、重複エラーが起こる
ちなみに未削除のレコードとemailが重複する場合は、従来通り重複エラーが起こります。
INSERT INTO users (name, email, password, created_at, updated_at)
VALUES ('田中三郎', 'tanaka@example.com', SHA2('password', 256), NOW(), NOW());
SQLエラー [1062] [23000]: Duplicate entry 'tanaka@example.com-1' for key 'users.users_email_exist_unique'
なので、未削除のレコードと重複が起こることはありません。
参考
gihyo.jp | Generated Columnを利用してみる
やばブロ!|【Laravel】論理削除とユニーク制約を両立させる【MySQLとMariaDB】
Qiita|論理削除と一意性制約を両立させる方法・DB製品別