はじめに
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を使用する際は注意が必要なので是非参考にしてみて下さい！