2026-09-12 · 6 min read · Rahul Chitturi
- sql
- collation
- data-integrity
An insert for User@example.com may conflict with user@example.com in one database but succeed in another. Collations can control case, accents, locale rules, and character equivalence, while application code may normalize the same identifier differently.
A display string, login identifier, and database key are separate concerns. Lowercasing everything without a defined Unicode and locale policy can create new mismatches.
Inspect the actual comparison contract
Record the database engine and version, column type and collation, index expression, connection settings, parameter type, and application normalization. Query catalog metadata rather than assuming the database default reaches every column.
- Check whether the unique index uses the column or an expression.
- Distinguish case sensitivity from accent sensitivity.
- Include trailing-space and normalization-form behavior.
- Review tenant and soft-delete predicates in the key.
Choose one canonical identity rule
Define equivalence from the product requirement, then enforce it in one durable database constraint. Preserve the user's preferred display spelling separately when necessary.
Use engine-supported case-insensitive types, a deliberate collation, or a normalized generated value according to measured behavior. Handle unique violations as concurrency outcomes instead of relying on a pre-insert existence check.
Build a cross-layer fixture matrix
Use the SQL formatter to inspect schema and queries, case converter for ASCII naming fixtures, diff for normalized values, and JSON formatter for synthetic API requests. Production comparison rules still require database tests.
Test ASCII case, accents, composed and decomposed Unicode, locale-sensitive letters, whitespace, maximum length, existing duplicates, concurrent inserts, driver upgrades, restores, replicas, and migrations between collations.