MySQLで論理削除とユニーク制限

Railsでレコードの論理削除を行うには[paranoia](https://github.com/rubysherpas/paranoia)のGemを使うなどありますが、ユニーク制限のあるレコードでは論理削除したものと新しく作成したものがバッティングしてしまうため期待通りに動作しません。

ユニーク制限をアプリケーション側で行うのも手ですが、なんとかデータベースレベルで制限できないかということで、論理削除を考慮したユニーク制限を考えてみました。

まず前提知識として、ユニーク制限は`NULL`のカラムでは適用されません。
それをうまく利用します。

`users`が`UNIQUE KEY`の`name`と論理削除用の`deleted_at`を持っているとして、

```rb:db/migrate/20160225213212_create_users.rb
class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t| 
      t.string :name
      t.datetime :deleted_at

      t.timestamps null: false
    end 
    add_index :users, :name, unique: true
  end
end
```

```rb:db/migrate/20160225213213_logical_delete_users.rb
class LogicalDeleteUsers < ActiveRecord::Migration
  def up
    add_column :users, :logical_uniqueness, :boolean, default: true
    remove_index :users, :name
    add_index :users, [:name, :logical_uniqueness], unique: true
    execute <<-SQL
CREATE TRIGGER `set_logical_uniqueness_on_users` BEFORE UPDATE ON `users`
  FOR EACH ROW
  BEGIN
    IF NEW.`deleted_at` IS NULL THEN
      SET NEW.`logical_uniqueness` = TRUE\;
    ELSE
      SET NEW.`logical_uniqueness` = NULL\;
    END IF\;
  END;
    SQL
  end 

  def down
    execute <<-SQL
DROP TRIGGER `set_logical_uniqueness_on_users`;
    SQL
    remove_index :users, [:name, :logical_uniqueness]
    add_index :users, :name, unique: true
    remove_column :users, :logical_uniqueness
  end
end
```

`deleted_at`が`NULL`でなくなったタイミングで`logical_uniqueness`を`NULL`にすることで、ユニーク制限の対象から外しています。

```rb
[0] 2.0.0-p481(main)> user = User.create!(name: "test")
+----+------+------------+-------------------------+-------------------------+--------------------+
| id | name | deleted_at | created_at              | updated_at              | logical_uniqueness |
+----+------+------------+-------------------------+-------------------------+--------------------+
| 1  | test |            | 2016-02-25 22:03:33 UTC | 2016-02-25 22:03:33 UTC | true               |
+----+------+------------+-------------------------+-------------------------+--------------------+

[1] 2.0.0-p481(main)> User.create!(name: "test")
=> ActiveRecord::RecordNotUnique: Mysql2::Error: Duplicate entry 'test-1' for key 'index_users_on_name_and_logical_uniqueness': INSERT INTO `users` (`created_at`, `deleted_at`, `logical_uniqueness`, `name`, `updated_at`) VALUES ('2016-02-25 22:07:42', NULL, 1, 'test', '2016-02-25 22:07:42')

[2] 2.0.0-p481(main)> user.destroy
+----+------+-------------------------+-------------------------+-------------------------+--------------------+
| id | name | deleted_at              | created_at              | updated_at              | logical_uniqueness |
+----+------+-------------------------+-------------------------+-------------------------+--------------------+
| 1  | test | 2016-02-25 22:08:26 UTC | 2016-02-25 22:03:33 UTC | 2016-02-25 22:03:33 UTC | true               |
+----+------+-------------------------+-------------------------+-------------------------+--------------------+

[3] 2.0.0-p481(main)> User.create!(name: "test")
+----+------+------------+-------------------------+-------------------------+--------------------+
| id | name | deleted_at | created_at              | updated_at              | logical_uniqueness |
+----+------+------------+-------------------------+-------------------------+--------------------+
| 3  | test |            | 2016-02-25 22:09:43 UTC | 2016-02-25 22:09:43 UTC | true               |
+----+------+------------+-------------------------+-------------------------+--------------------+

[4] 2.0.0-p481(main)> User.unscoped.all    
+----+------+-------------------------+-------------------------+-------------------------+--------------------+
| id | name | deleted_at              | created_at              | updated_at              | logical_uniqueness |
+----+------+-------------------------+-------------------------+-------------------------+--------------------+
| 1  | test | 2016-02-25 22:08:26 UTC | 2016-02-25 22:03:33 UTC | 2016-02-25 22:03:33 UTC |                    |
| 3  | test |                         | 2016-02-25 22:09:43 UTC | 2016-02-25 22:09:43 UTC | true               |
+----+------+-------------------------+-------------------------+-------------------------+--------------------+
```

このように、論理削除する前はユニーク制限がかかっており、論理削除したレコードはユニーク制限の対象から外れたことがわかります。
MySQLの仕様上`id`はインクリメントされてしまうようです。

なお、今回は`TRIGGER`によるパフォーマンスの低下やMySQLでこういった処理をやることの妥当性は考慮していません。