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.

Rakesh Patidar · Answer 1 · 2016-09-05T15:28:27.913

3

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

edited Sep 05 '16 at 15:28

answered Sep 05 '16 at 14:56

Rakesh Patidar

176
1
10

I am using postgres which supports partial indexes, `CREATE UNIQUE INDEX customer_name_index ON customer(name) WHERE removed_on IS NULL;`. How do i get something like this? What would be the migration? @Rakesh – Swapnil Devesh Sep 05 '16 at 15:20
Did you try above solution? – Rakesh Patidar Sep 05 '16 at 15:57
Could you please explain why you did remove_index before add_index? Is an index added by default? @RakeshPatidar – Swapnil Devesh Sep 05 '16 at 16:04
@SiD You are using devise and it added an index to email by default. Now because we are adding index on combination of email and deleted_at fields so I removed from single email. – Rakesh Patidar Sep 05 '16 at 18:16
5

I solved this issue using the following migration: class `AddPartialIndexToUsers < ActiveRecord::Migration def change remove_index :users, column: :email add_index :users, :email, unique: true, where: "deleted_at = NULL" add_index :users, :phoneno, unique: true, where: "deleted_at = NULL" end end` This seems better to me since in your migration I don't specifically need deleted_at to be unique....this seems more what I required...If I am wrong somewhere mention it, or update your asnwer so I can accept it @Rakesh – Swapnil Devesh Sep 05 '16 at 18:26
1

I believe the solution above won't work. See [this doc](https://www.postgresql.org/docs/9.0/indexes-unique.html). Because NULL != NULL, you could insert ("a@b.com", NULL) and ("a@b.com", NULL) again and the constraint will allow it. I think SiD Devesh's answer is correct. – smoyth Nov 28 '18 at 21:09
And also it should be `where: "deleted_at IS NULL" ` not `=` sign. – bgr11n Nov 27 '19 at 09:55

score 1 · Answer 2 · answered Apr 13 '21 at 16:04

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

score 1 · Answer 3 · answered May 31 '21 at 12:59

1

just enough:

class AddUniqueIndexToUsers < ActiveRecord::Migration
  def change
    remove_index :users, column: :email
    add_index :users, :email, unique: true, where: 'deleted_at IS NULL'
  end
end

answered May 31 '21 at 12:59

yunixon

71
4

score 0 · Answer 4 · answered Mar 24 '21 at 12:18

Rakesh's answer is wrong and will allow duplicates in the database because a compound UNIQUE index will fail when one of the values is NULL (and deleted_at is NULL by default).

To make this work you actually need to use this nuance with unique indexes in the opposite way, you can have an integer column, like acive:tinyint (default 1) and a compound index like this:

add_index :users, [:email, :active], unique: true

No special actions required when creating new users as the active column has a default value of 1. But every time you remove a record, in addition to setting the deleted_at you also need to set the active to NULL. This way you can have just one active record and many deleted with the same email address.

In PostgreSQL or MySQL > 8.0.13 you can work without this additional column and use a functional index like this:

CREATE UNIQUE INDEX unique_email
                 ON users (email, (IF(deleted_at IS NULL, 1, NULL)));

The result will be similar to the example with the active column. Once a record is deleted the unique index will stop working for this record allowing you to have multiple deleted records with the same email.

Unique constraint issue paranoia gem

4 Answers4