Laravel

【Laravel】Generated Columnを利用し論理削除とユニーク制約を両立させる

こんにちは、タクマです。

今回は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');
    }
}

上記マイグレーションファイルをマイグレートすると以下のようなテーブルになります。

FieldTypeNullKeyDefaultExtra
idbigint unsignedNOPRIauto_increment
namevarchar(191)YES
emailvarchar(191)NOUNI
passwordvarchar(191)NO
created_attimestampYES
updated_attimestampYES
deleted_attimestampYES

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;
idnameemailpasswordcreated_atupdated_atdeleted_at
1田中太郎tanaka@example.com5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d82022-09-10 21:02:182022-09-10 21:02:182022-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;
idnameemailpasswordcreated_atupdated_atdeleted_atexist
1田中太郎tanaka@example.com5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d82022-09-11 10:29:212022-09-11 10:29:212022-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;
idnameemailpasswordcreated_atupdated_atdeleted_atexist
1田中太郎tanaka@example.com5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d82022-09-11 10:29:212022-09-11 10:29:212022-09-11 10:29:21
2田中二郎tanaka@example.com5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d82022-09-11 10:38:522022-09-11 10:38:521

未削除の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製品別

Laravel 8.x マイグレーション

Webエンジニア
タクマ
埼玉県出身の33歳

新卒で入社した専門商社で8年間営業職として勤務

30歳からプログラミングを始め31歳でWebエンジニアに転職成功

受託開発企業での開発を1年弱経験したのち、現在はスタートアップの自社開発企業で開発に従事している
\ Follow me /

COMMENT

メールアドレスが公開されることはありません。

CAPTCHA