2026-09-12 · 8 min read
- sql
- collation
- data-integrity
A text identity rule answers whether two spellings represent the same account name, email-like identifier, tag, or external key. SQL collations can compare case, accents, width, locale-specific characters, and normalization differently, so a generic request for case-insensitive uniqueness is incomplete.
The durable constraint must match the product's equivalence rule. Application checks alone race under concurrent inserts and can drift from database comparison after runtime or Unicode upgrades.
Write the equivalence contract first
State which field is an identifier, which transformations are allowed, whether comparison is locale-independent, and whether accents, width variants, whitespace, or Unicode normalization matter. Keep presentation spelling separate from the canonical comparison key when users expect their input to be preserved.
- Do not apply locale-sensitive lowercasing without a named locale.
- Do not assume email local parts or external IDs share one rule.
- Define maximum length before and after normalization.
- Include tenant, status, and soft-delete scope in identity.
Choose a database enforcement strategy
Depending on the engine, use a reviewed collation, case-insensitive text type, functional unique index, or generated canonical column. Pin or document collation versions when upgrades can change comparison weights.
Query catalog metadata to verify the effective collation and index expression. Connection defaults and query-level collations can change lookups without changing the unique constraint, creating confusing read-versus-write behavior.
Handle concurrency and migration explicitly
Attempt the insert or update and classify a unique-constraint violation as the authoritative conflict. A preceding availability query improves user feedback but cannot reserve the value.
- Scan existing rows under the proposed new comparison rule.
- Resolve collisions before creating the constraint.
- Build and validate indexes using the engine's safe migration path.
- Keep old and new application versions compatible during rollout.
Maintain a Unicode fixture suite
Use Flashman's SQL formatter for schema and query review, case converter for simple naming fixtures, diff for canonical values, JSON formatter for synthetic API models, and number-base converter to inspect public code points. Database behavior remains authoritative.
Test ASCII case, accents, composed and decomposed forms, dotted and dotless letters, sharp s, ligatures, width variants, spaces, empty values, nulls, maximum lengths, concurrent writes, bulk imports, backups and restores, replicas, engine and collation upgrades, and rollback from a failed migration.