validates value uniqueness issue

[schinery]

Given the following:

class Client
  acts_as_paranoid without_default_scope: true

  validates :email, presence: true, uniqueness: { case_sensitive: false }
end

create_table "clients", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.string "email", null: false
  t.datetime "deleted_at"
  t.index "lower((email)::text)", name: "index_clients_on_lower_email", unique: true
end

If I try and create a new client with the same email as a "deleted" client I get the following error:

ActiveRecord::RecordNotUnique (PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_clients_on_lower_email"
DETAIL:  Key (email)=(foo@example.com) already exists.
)

What I was expecting to happen was that it would trigger an "email is already taken" validation error because I have without_default_scope: true, so it should check against all clients, not just the non-deleted ones.

Is this what should be happening and something is wrong, or am I incorrect and need to do something else to validate against all clients.

I'm using:

Ruby 3.3.0
Rails 7.1.3.2
Paranoia 2.6.3

[schinery]

Also, I saw that there was #500, which looks like a similar thing but I didn't want to hijack that for my own purposes.

[mathieujobin]

kind of a boring workaround, but you could add :deleted_at column in the constraint.
what do you think ?

[myabc]

If you're using PostgreSQL you should use partial indexes (see docs). Try replacing:

t.index "lower((email)::text)", name: "index_clients_on_lower_email", unique: true

with

t.index "lower((email)::text)", name: "index_clients_on_lower_email", unique: true, where: 'deleted_at IS NOT NULL'

[dhanajit96]

I face the same issue