In Active Record, what do scope: and case_sensitive: change in validates :username, uniqueness:, and what query does the check run?
answer
- SELECT 1 ... LIMIT 1 before save
- persisted record excludes itself
- scope: adds columns to the WHERE
- case_sensitive: false uses LOWER()
- default follows the column collation
basics
~20 suniqueness 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 linesclass 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 leaguego deeper
Recall that uniqueness checks the table for another row with the same value and adds "has already been taken".
Explain the query, how scope: and case_sensitive: change it, and how a persisted record excludes itself.
Match the validator's rule to the index that backs it, including case handling and nil scope columns.
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.