validates_uniqueness_of is not a data-integrity mechanism. It is a user-experience mechanism, and treating it as anything more is how you end up with duplicate invoices. The failure mode is embarrassingly easy to reproduce: two requests pass the validation check, then both INSERT, and the second one doesn’t care that you told the first one it was fine. Any two processes that check before they write have a check-then-act race, and no amount of model code fixes that. Only the database can.

We shipped a redemption system with a validates :code, uniqueness: true and no unique index. For two weeks it quietly minted duplicate redemptions under load testing, and we found them when a customer redeemed the same promo code twice. The fix was a one-line migration, and the lesson stuck: validations give nice error messages, constraints give guarantees.

The constraint layer

The multi-tier approach is simple in principle: model validations for instant, friendly feedback; database constraints for the invariant nobody gets to violate. For most tables that means three things:

• A unique index for anything validated with uniqueness:.
• A foreign key with an explicit on_delete behavior.
• A check constraint for range invariants like non-negative balances.

1
2
3
4
5
6
7
8
9
10
class AddIntegrityConstraintsToRedemptions < ActiveRecord::Migration[6.0]
  def change
    add_index :redemptions, [:account_id, :external_code], unique: true
    add_foreign_key :redemptions, :accounts, on_delete: :restrict
    execute <<~SQL
      ALTER TABLE accounts
        ADD CONSTRAINT balance_non_negative CHECK (balance >= 0)
    SQL
  end
end

Rails 6.0 has no add_check_constraint helper (that landed in 6.1), so for now it’s raw DDL through execute. The unique index on (account_id, external_code) is the part that actually closes the race, because Postgres enforces it atomically at insert time: exactly one of two concurrent inserts wins.

The exception contract

Constraint failures surface as ActiveRecord::RecordNotUnique, ActiveRecord::InvalidForeignKey, and friends, all subclasses of StatementInvalid. That means they do not integrate with errors the way validations do, so you translate at the service boundary:

1
2
3
4
5
6
7
class RedeemService
  def call(account, code)
    Redemption.create!(account: account, external_code: code)
  rescue ActiveRecord::RecordNotUnique
    raise AlreadyRedeemed, "code #{code} is taken"
  end
end

Rescuing and mapping to a domain error beats letting a StatementInvalid bubble up to a 500. Pick the boundary deliberately, too: rescuing in a controller couples the error handling to the view, and swallowing constraint errors silently is how data rot starts.

Adding constraints to big tables without an outage

The catch is that adding constraints is itself a table-locking operation, and the lock scales with the number of rows scanned. On a table with tens of millions of rows, a plain add_index or ADD CONSTRAINT ... CHECK can block writes for minutes while it scans.

For a unique index, build it concurrently and out of a transaction:

1
2
3
4
5
6
7
class AddUniqueIndexOnExternalCode < ActiveRecord::Migration[6.0]
  disable_ddl_transaction!
  def change
    add_index :redemptions, [:account_id, :external_code],
              unique: true, algorithm: :concurrently
  end
end

For a check constraint on a large table, add it NOT VALID (Postgres skips the table scan and acquires only a brief lock), then validate it later when it’s cheap:

1
2
execute "ALTER TABLE accounts ADD CONSTRAINT balance_non_negative CHECK (balance >= 0) NOT VALID"
execute "ALTER TABLE accounts VALIDATE CONSTRAINT balance_non_negative"

VALIDATE CONSTRAINT re-scans but takes a weaker lock, so it can run alongside traffic. A 40-million-row validation still took about a minute in our case, but nothing queued behind it.

What constraints can’t do

Constraints can’t give your customers a friendly error, and they can’t express business rules that depend on state outside the row — a balance can’t go negative, but whether a discount applies depends on rules you don’t want encoded in DDL. Keep both layers: validate in the model for UX, constrain in the database for truth. Two rules of thumb:

• Every uniqueness: validation needs a matching unique index, or the validation is decoration. Also remember Postgres treats NULLs as distinct in unique indexes; a partial unique index (where: "email IS NOT NULL") is the standard fix.
• Money and ledger tables get on_delete: :restrict or deferrable constraints, never :nullify. Orphaning financial history to keep a delete convenient is a bad trade.

One more production note: adding a unique index and then discovering it fails because duplicates already exist is the most honest thing Postgres will ever do for you. Run a duplicate-detection query before the migration so that discovery happens in a query you control, not mid-deploy. And write a concurrency test: two threads, a latch, both trying to create the same record, asserting exactly one succeeds. That test has caught more regression than any code review on our team.