【Mysql】Generated Columnを使ったactiveなデータに対してユニーク制約を設定する方法 #Laravel

アクティブなデータにのみユニーク制約をかけたい時の実装方法として、Generated Columnを使った複合ユニーク制約の掛け方について記載します。

前提として、Mysql ver5.7以降である必要があるのでご注意を。

手順

・Generated column(仮想生成カラム)を定義
・ユニークにしたいカラムと組み合わせた複合ユニーク制約をかける

例

以下はアクティブなユーザーのメールアドレスのみユニークにしたい、というケースの実装例です。

.php

<?php declare(strict_types=1);

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('is_active')->nullable()->virtualAs('IF(ISNULL(deleted_at), 1, NULL)');

            $table->unique(['email', 'is_active']);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropUnique(['email', 'is_active']);
            $table->dropColumn('is_active');
        });
    }
};

生のALTER文なら以下のようになります。

.sql

ALTER TABLE `users` ADD `is_active` varchar(255) as (IF(ISNULL(deleted_at), 1, NULL)); 
ALTER TABLE `users` ADD UNIQUE `users_email_is_active_unique`(`email`, `is_active`);

unique制約がかかっている状態においてもnullの値のカラムの複数存在は許容されるので、
・is_active = 1かつ同じemailのデータ常に1つであるべき
・is_active = nullの場合は同じemailを許容する

という制約になります。
データ例↓

+----+------------------------+---------------------+-----------+
| id | email                  | deleted_at          | is_active |
+----+------------------------+---------------------+-----------+
|  1 | hoge@example.com       | 2023-02-18 05:03:55 | NULL      |
|  2 | hoge@example.com       | 2023-02-18 05:04:12 | NULL      |
|  3 | hoge@example.com       | NULL                | 1         |
+----+------------------------+---------------------+-----------+

補足

Generated columnの値が2とか3とか、1以外、になったら意図に反したデータができちゃうんじゃないか？
と思ったのですが、その心配はありませんでした。

INSERT文、UPDATE文でGenerated columnの値を設定しようとすると、ちゃんとエラーを吐いてくれます。

Error Code: 3105. The value specified for generated column 'is_active' in table 'users' is not allowed.

カラム定義に含まれる式から値が計算されるカラム。カラム値は格納されませんが、BEFORE トリガーの直後に行が読み取られたときに評価されます。仮想生成カラムは記憶域を取りません。 InnoDB では、仮想生成カラムのセカンダリインデックスがサポートされます。
MySQL 用語集:仮想生成カラム

↑の通り、Generated Columnはそもそも実際にデータを格納するカラムではないため、クエリで値を入れることができないようです。
LaravelのEloquent ORMで言うところのアクセサみたいですね。

【Mysql】Generated Columnを使ったactiveなデータに対してユニーク制約を設定する方法

手順

例

補足

参考