deleted_atカラムを含む複合unique indexの作成方法(ruby, PostgreSQL)


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

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

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

```ruby
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
```ruby
class AddUniqueIndexToUsers < ActiveRecord::Migration[6.1]
  def change
    add_index :users, :mail_address, unique: true
  end
end
```

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

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

```ruby
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
```ruby
class AddUniqueIndexToUsers < ActiveRecord::Migration[6.1]
  def change
    add_index :users, [:mail_address, :deleted_at], unique: true
  end
end
```

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

```ruby
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を登録してみましょう。
これで登録できなければ問題ないです！

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

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

理由は[こちら](https://www.postgresql.jp/document/9.4/html/indexes-unique.html)になります。
`NULL値は同じ値とはみなされません。` ということです。

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

```ruby
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' という値へ置き換えていますが、これは[こちら]([https://www.postgresql.jp/document/9.1/html/datatype-datetime.html](https://www.postgresql.jp/document/9.1/html/datatype-datetime.html))へも記載されていますが特殊な値で「他のすべてのタイムスタンプより将来」というものです。
つまり実際に `deleted_at` へ値が入っているデータと被ることがない、かつNULLでない(置換されているので)ためuniqueとすることができます。

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

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