Add support for constraint such as exclusion

[jamesst20]

Hi,

I have a case scenario where I can assign multiple credentials but only one can be set as "primary" for a server but any amount can be non-primary.

class Server < ApplicationRecord
  has_many :credentials, dependent: :destroy
  accepts_nested_attributes_for :credentials, reject_if: :all_blank, allow_destroy: true
end

class Credential < ApplicationRecord
  belongs_to :server
  validates :primary, db_uniqueness: { scope: :server_id, where: '("primary" = true)', rescue: :always }
end

To reflect that, I could do

add_index :credentials, %i[server_id primary], unique: true, where: '"primary" = true'

However, if within the same form, I were to toggle primary off of the first record and toggle it on on the second record, the save would fail because it violates the unique constraint. I would need to turn all primary switch off first, then save, then re-edit and toggle on the new one.

With postgres there are deferred constraint that can wait until the end of a transaction before executing. This is supported since Rails 7.1. Indexes don't support deferrable but constraints do. Rewriting the previous index as a constraint would look like so:

    add_exclusion_constraint :credentials, '"server_id" WITH =, "primary" WITH =',
                             where: '"primary" = true',
                             using: :btree,
                             deferrable: :deferred

However, the gem is not able to find the appropriate index and raise DatabaseValidations::Errors::IndexNotFound.
Also, if both primary were to be off in database and I were to turn both ON in my form and then save, I would get an exception even with the rescue: :always