はじめに

railsのアプリケーションで論理削除を使っている場合にあるカラムへunique indexを作成する時の注意点と作成方法を記載します。

前提

databaseはPostgreSQLを使用している前提で記載します。

以下のようなテーブルへunique indexを作成したい場合を考えます。
unique indexを作成したいカラムは mail_address でレコードの削除は gem paranoia を使って論理削除していることとします。

class CreateUsers < ActiveRecord::Migration[6.1]
  def change
    create_table :users do |t|
      t.string   :first_name
      t.string   :last_name
      t.string   :mail_address, null: false
      t.datetime :deleted_at

      t.timestamps
    end
  end
end

bad unique index 例1

class AddUniqueIndexToUsers < ActiveRecord::Migration[6.1]
  def change
    add_index :users, :mail_address, unique: true
  end
end

badな理由

これだと論理削除したレコードの mail_address を使って新規に登録することができません。

以下のようなエラーとなります。

user = User.create(first_name: "hogehoge", last_name: "fugafuga", mail_address: "hogehoge@example.com")

user.destroy!

user = User.create(first_name: "hogehoge2", last_name: "fugafuga2", mail_address: "hogehoge@example.com")

ActiveRecord::RecordNotUnique (PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_users_on_mail_address")
DETAIL:  Key (mail_address)=(hogehoge@example.com) already exists.

bad unique index 例2

class AddUniqueIndexToUsers < ActiveRecord::Migration[6.1]
  def change
    add_index :users, [:mail_address, :deleted_at], unique: true
  end
end

badな理由

まず最初に例1で試した論理削除したレコードの mail_address を使って新規に登録できるか確認します。

user = User.create(first_name: "hogehoge", last_name: "fugafuga", mail_address: "hogehoge@example.com")

user.destroy!

user = User.create(first_name: "hogehoge2", last_name: "fugafuga2", mail_address: "hogehoge@example.com")

通りました！
一見正常にunique indexが作成されているように見えます。

ではもう1レコード同じmail_addressのuserを登録してみましょう。
これで登録できなければ問題ないです！

user = User.create(first_name: "hogehoge3", last_name: "fugafuga3", mail_address: "hogehoge@example.com")

はい、登録できてしまいました😇

理由はこちらになります。
NULL値は同じ値とはみなされません。 ということです。

解決策

ではどのようにunique indexを作成すれば良いかですが、以下のunique index作成migrationであれば問題なく動作します。

class AddUniqueIndexToUsers < ActiveRecord::Migration[6.1]
  def up
    execute <<-SQL.squish
      CREATE UNIQUE INDEX "index_mail_address_unique" ON "users"
        ("mail_address", COALESCE(deleted_at, 'infinity'))
    SQL
  end

  def down
    execute <<-SQL.squish
      DROP INDEX "index_mail_address_unique"
    SQL
  end
end

COALESCEは該当のカラムがNULLであった場合に任意の値に置き換えるという関数です。

ここで 'infinity' という値へ置き換えていますが、これはこちらへも記載されていますが特殊な値で「他のすべてのタイムスタンプより将来」というものです。
つまり実際に deleted_at へ値が入っているデータと被ることがない、かつNULLでない(置換されているので)ためuniqueとすることができます。

では実際にuniqueになっているか確認します。

user = User.create(first_name: "hogehoge", last_name: "fugafuga", mail_address: "hogehoge@example.com")

user.destroy!

user = User.create(first_name: "hogehoge2", last_name: "fugafuga2", mail_address: "hogehoge@example.com")

user = User.create(first_name: "hogehoge3", last_name: "fugafuga3", mail_address: "hogehoge@example.com")

ActiveRecord::RecordNotUnique (PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_mail_address_unique")
DETAIL:  Key (mail_address, (COALESCE(deleted_at, 'infinity'::timestamp without time zone)))=(hogehoge@example.com, infinity) already exists.

問題なさそうでした！

最後に

論理削除を使用しているアプリケーションでunique indexを使用する際は注意が必要なので是非参考にしてみて下さい！