skip to content

In Active Record, what do scope: and case_sensitive: change in validates :username, uniqueness:, and what query does the check run?

level: middleimportance: should knowfreq 60%

answer

  1. SELECT 1 ... LIMIT 1 before save
  2. persisted record excludes itself
  3. scope: adds columns to the WHERE
  4. case_sensitive: false uses LOWER()
  5. default follows the column collation

basics

~20 s

uniqueness runs SELECT 1 ... LIMIT 1 for another row with the same value (excluding the record itself); scope: adds equality conditions on other columns, and case_sensitive: false compares with LOWER() instead of the column's collation.

solid answer

~40 s

`validates :username, uniqueness: true` runs `SELECT 1 AS one FROM players WHERE username = ? LIMIT 1` before the save, adding `AND id != ?` for a persisted record, and adds the `:taken` error ("has already been taken") if a row comes back. `scope: :league_id` (or an array) adds `AND league_id = ?` with the record's own value, so the same username may exist in two leagues. `case_sensitive:` is unset by default, which means the database's collation decides; `case_sensitive: false` wraps both sides in `LOWER()` (unless the column's collation is already case-insensitive), so `CoachAmy` collides with `coachamy`. `conditions: -> { where(active: true) }` narrows the rows considered. None of this reserves the value: it is a read followed by a write.

code

ruby · 11 lines
ruby
class Player < ApplicationRecord
  belongs_to :league
  validates :username, uniqueness: { scope: :league_id, case_sensitive: false }
end

Player.create!(league_id: 7, username: "CoachAmy")
dup = Player.new(league_id: 7, username: "coachamy")
dup.valid?                     # => false
dup.errors.details[:username]  # => [{error: :taken, value: "coachamy"}]

Player.new(league_id: 8, username: "coachamy").valid? # => true, other league

go deeper

for a junior

Recall that uniqueness checks the table for another row with the same value and adds "has already been taken".

for a middle

Explain the query, how scope: and case_sensitive: change it, and how a persisted record excludes itself.

for a senior

Match the validator's rule to the index that backs it, including case handling and nil scope columns.

for a principal

Decide where uniqueness is defined, in normalized values, indexes and validations, so the rule cannot drift between layers.

## What the validator does In the youth-sports league app every player picks a username. The model says: ```ruby validates :username, uniqueness: { scope: :league_id, case_sensitive: false } ``` The **uniqueness validator** is an Active Record validator (it needs the table), and it works by **querying** before the save: 1. Build a relation on the model's table, ignoring default scopes. 2. Add a condition on the attribute's value. 3. For a persisted record, exclude the record itself by its primary key value in the database, so saving an unchanged player does not collide with its own row. 4. Add the `scope:` and `conditions:` constraints. 5. Run an existence query; if a row exists, add `:taken` to `errors[:username]`. The SQL for a new player looks like: ```sql SELECT 1 AS one FROM "players" WHERE LOWER("players"."username") = LOWER('CoachAmy') AND "players"."league_id" = 7 LIMIT 1 ``` ## scope: `scope:` takes a symbol or an array of symbols naming other columns (an association name resolves to its foreign key). Each one adds an equality condition using the **record's own value**: | Declaration | Meaning | |---|---| | `uniqueness: true` | unique across the whole table | | `uniqueness: { scope: :league_id }` | unique within one league | | `uniqueness: { scope: [:league_id, :season_id] }` | unique within one league and season | A scope column that is `nil` is compared as `IS NULL`, so two players with no league collide with each other. ## case_sensitive: - **Not given** (the default): the comparison is a plain `=`, so the **column's collation** decides. With a case-sensitive collation (the usual PostgreSQL and SQLite setup), `CoachAmy` and `coachamy` are different usernames. - **`case_sensitive: false`**: both sides are wrapped in `LOWER()`, unless the adapter sees the column already uses a case-insensitive collation, in which case plain `=` is enough. - **`case_sensitive: true`**: forces an exact match on databases whose collation would otherwise ignore case. For usernames, case-insensitive uniqueness is usually what people expect, and normalizing the value on assignment (lower-casing it before validation) makes the check and any index simpler; normalization itself is a separate macro. ## conditions: `conditions:` takes a callable that narrows the rows considered: ```ruby validates :jersey_number, uniqueness: { scope: :team_id, conditions: -> { where(active: true) } } ``` A retired player's number can be reused. A lambda with one argument receives the record, for rules that depend on its own values. ## Messages and options - The error type is `:taken`, and the default message is "has already been taken". - `message:`, `allow_nil:`, `allow_blank:`, `on:`, `if:` and `unless:` work as on any validator. - `allow_nil: true` is common for optional unique values such as an external registration number. ## A query-saving detail for updates On a **persisted** record, when neither the attribute nor any scope column changed (and no `conditions:` or `case_sensitive:` option is set), the validator skips its query if a unique index already covers those columns. The database guarantees what the query would check. A new record is always checked. ## What the validator cannot do The query and the INSERT are separate statements, so two requests can both see "no such username" and both insert. The validator gives users a friendly message in the common case; only a unique index in the database stops the race. ## Testing the rule A model test for a scoped, case-insensitive rule needs three cases: 1. **Same league, different case**: create `CoachAmy` in league 7, build `coachamy` in league 7, assert `errors.of_kind?(:username, :taken)`. 2. **Other league**: build `coachamy` in league 8 and assert it is valid. 3. **Update of the existing record**: change another attribute on the saved `CoachAmy` and assert it is still valid, proving the record excludes itself. These tests prove the validator's query, not the absence of duplicates: a concurrency test or a database constraint test covers that separately.

  • Two players have league_id nil and the same username. Does scope: :league_id let both save?
    No. The scope condition uses the record's own value, so a `nil` league becomes `league_id IS NULL` and the existing nil-league player is found. If unassigned players should not collide, add `conditions:` or require the league.
  • Why does saving an unchanged player not report its own username as taken?
    For a persisted record the validator adds `WHERE id != ?` using the record's primary key value in the database, so its own row is excluded from the existence query.

  • Saying uniqueness: true creates a unique index in the database.
  • Assuming case_sensitive defaults to false and CoachAmy collides with coachamy everywhere.
  • Believing scope: :league_id makes usernames unique across all leagues.
  • Expecting the validator to lock the username until the save commits.
  • Thinking a persisted record will always collide with its own row.