MySQLでGenerated Columnによる複合ユニーク制約の設定

前提

• バックエンド：Ruby on Rails(特にバージョンの指定はないが6系以上を想定)
• データベース：MySQL(5.7以上)

発生しうる問題

論理削除が設定されているテーブルで、[:name, :deleted_at]の組み合わせでユニーク制約をかけている場合、insert文で直接DBにデータを入れる時に、このユニーク制約が機能しません。

Model側でバリデーション設定をしている場合、アプリケーション側でModelが検知できる状況であれば

この問題は発生しません。

class User < ApplicationRecord
  validates :name, uniqueness: { scope: :deleted_at }
end

add_index :users, [:name, :deleted_at], unique: true

Rails側では `nil == nil` でtrueが成立するが、DB側では NULLはNULLに一致せず、ユニーク制約の対象外となります。

たとえば、rakeタスク用に作成したコードで、処理時間を考慮しActive Recordを使用せずに生のSQL文で実装する時などは注意が必要です。

今回の例だと、

• name: “テストname”, deleted_at: NULL
• name: “テストname”, deleted_at: NULL

とnameが重複したレコードの保存が出来てしまいます。

この場合にMySQLのGenerated Columnの使用が検討できます。

Generated Columnsの適用方法

マイグレーションファイルの作成例です。今回は既存のスキーマ定義を変更する想定です。

usersテーブルに新たにactiveというboolean型のカラムを追加しています。そして、このactiveカラムは deleted_atが NULL のときは1（true）を、そうでない場合は NULLを自動的に生成します。

class AddIndexToUsers < ActiveRecord::Migration[6.1]
  def up
    remove_index :users, [:name, :deleted_at], unique: true

    execute "ALTER TABLE users ADD COLUMN active boolean GENERATED ALWAYS AS (CASE
      WHEN deleted_at is NULL THEN 1
      ELSE NULL
    END) STORED;"

    add_index :users, [:name, :active], unique: true
  end

  def down
    remove_index :users, [:name, :active], unique: true
    remove_column :users, :active
    add_index :users, [:name, :deleted_at], unique: true
  end
end

マイグレーションを実行すると、Generated Columnが生成されます。

create_table "users", charset: "utf8mb4", force: :cascade do |t|
  〜省略〜
  t.string "name", null: false, collation: "utf8mb4_bin"
  t.integer "sort_order"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.datetime "deleted_at"
  t.virtual "active", type: :boolean, as: "case when `deleted_at` is null then 1 else NULL end", stored: true
  t.index ["deleted_at"], name: "index_users_on_deleted_at"
  t.index ["name", "active"], name: "index_users_on_name_and_active", unique: true

スキーマファイルでの定義を見ると、activeカラムは仮想（virtual）カラムとして定義されていますが、stored: trueというオプションが指定されているため、このカラムはStored Generated Columnとなります。

Gerenated Columnは2種類あります。

• Virtual Generated Column：仮想生成カラムは、他のカラムから動的に計算される値を持ちます。この値はデータベース内に物理的に保存されず、必要に応じて計算されます。したがって、ディスクスペースは消費しませんが、データを取得するたびに計算を必要とします。
• Stored Generated Column：保存された生成カラムも、他のカラムから計算される値を持ちますが、この値はデータベース内に物理的に保存されます。そのため、ディスクスペースを消費しますが、データ取得時の計算は不要です。

今回のケースではデータ取得の際のパフォーマンスを考慮し、Stored Generated Columnを採用しています。

Stored Generated Columnの挙動イメージ