Email addresses are stored with the casing the user typed, but the product treats [email protected] and [email protected] as the same account. How do you make the database enforce that uniqueness?

## The problem The stored value and the value the rule applies to are different. Users expect their address back as typed, but two rows differing only in case must be rejected. A plain `UNIQUE (email)` compares the raw strings under the column's collation, so with a normal case-sensitive collation `[email protected]` and `[email protected]` are two distinct values and both are accepted. This is the second classic reason (after conditional uniqueness) that a unique **index** can do something a unique **constraint** cannot: a constraint takes a list of columns, an index can take an expression. ## Option 1 — expression (functional) unique index ``` CREATE UNIQUE INDEX account_email_ci_idx ON account (lower(email)); ``` The index stores the computed key. On insert or update the engine evaluates the expression and probes for a duplicate, so the rule is enforced atomically like any other unique index. Nothing extra is stored in the table. Requirements and consequences: - The expression must be **deterministic/immutable** — same input, same output, forever. Engines refuse to index a volatile expression, and one that silently depends on session settings would corrupt the index when the setting changes. - **Query matching.** The optimizer uses this index only for predicates spelled as the indexed expression. `WHERE lower(email) = lower($1)` matches; `WHERE email = $1` does not and will seq-scan. Every read path has to be written the same way, which is a real discipline cost in a codebase and the single most common reason this design underperforms in production. - **Not a foreign-key target**, since it keys on a derived value rather than on the referenced column. - Violations report the index name, so name it meaningfully. ## Option 2 — stored generated column plus a unique constraint ``` ALTER TABLE account ADD COLUMN email_norm text GENERATED ALWAYS AS (lower(email)) STORED; ALTER TABLE account ADD CONSTRAINT account_email_norm_key UNIQUE (email_norm); ``` The normalization is materialised as a column, and uniqueness becomes an ordinary constraint on an ordinary column. Advantages: it is a real catalog constraint (visible to tooling, referenceable by a foreign key, dumped as part of the model), reads are plain equality on `email_norm` so no expression-matching discipline is needed, and you can select the normalized value directly. Cost: extra storage and one more column to keep out of API responses. Because the column is generated, it cannot drift from `email` — do not hand-maintain it in application code, which is where these designs usually rot. ## Option 3 — case-insensitive collation Declare the column with a collation that ignores case, then `UNIQUE (email)` is enough: the uniqueness comparison uses the column's collation, so the two casings collide. This reads best and needs no discipline at call sites. The catch is scope: the collation changes comparison and sort semantics for *every* operation on that column — equality, `ORDER BY`, joins, `LIKE`/pattern behaviour in some engines. That may be exactly what you want for an email column and exactly wrong for a case-sensitive identifier. Changing a column's collation later also requires rebuilding every index over it. Treat it as a modelling decision, not a local fix. ## Normalization is a semantic choice "Case-insensitive" is not one thing. Lowercasing is locale-dependent for some scripts, and Unicode has characters whose case folding is not a simple one-to-one map; full case folding and simple lowercasing differ. For email specifically the domain part is case-insensitive by spec while the local part technically is not — most products still fold the whole thing, but that is a product decision to make explicitly. Whatever you choose, define it once. The failure mode is a system where registration normalizes one way, login another, and the unique index a third: the database happily accepts a row nobody can log into. ## Backfilling onto existing data Building the index or adding the constraint fails if current rows already collide case-insensitively — likely, since the rule was not previously enforced. Find the collisions first (group by the normalized value having count > 1), decide a merge/rename policy with the product owner, apply it, then create the index. Do not "fix" it by dropping the uniqueness requirement.