Unique constraint issue paranoia gem

Question

I have a rails app in which i am using devise and paranoia gems.
I have a users table in postgres db which has unique validation on email column. I am using paranoia for soft delete, issue is when i delete a user and then tey to create a user using the email of the deleted user it throws an error PG::UniqueViolation: ERROR.
I have read about this and know that this could be solved using partial index feature of rails.

http://scottsmerchek.com/2015/08/03/taking-the-d-out-of-crud/

https://devcenter.heroku.com/articles/postgresql-indexes#partial-indexes

How do i implement this?
Sorry for bad formatting, typing from mobile.

Answer 1

Because you deleted user as soft delete so that email didn't remove from database, only an user attribute is_deleted was set to true.

To resolve PG::UniqueViolation: ERROR now you have to create unique index on both field email and deleted_at So your migration will be

class AddUniqueIndexToUsers < ActiveRecord::Migration
  def change
    remove_index :users, column: :email
    add_index :users, [:email, :deleted_at], unique: true
  end
end

Answer 2

It will fail on Postgresql, possible solution will be

class AddUniqueIndexToUsers < ActiveRecord::Migration
  def change
    remove_index :users, column: :email
    add_index :users, [:email, :deleted_at], unique: true, where: "deleted_at is null"
  end
end

Refer https://www.ironin.it/blog/partial-unique-indexes-in-postgresql-and-rails.html