Enforce AtlasMart identity invariants with unique and collation-aware indexes while handling optional/missing values and dirty historical duplicates safely.
Unique Indexes, Missing/Null Values, Collations, and Application Invariants
Batch heterogeneous writes safely, interpret partial success, compare ordered and unordered execution, and use modern cross-namespace bulk APIs without assuming all-or-nothing behavior.
Learning objectives
Use unique indexes as database-enforced invariants and interpret duplicate-key failures as correctness signals.
Explain how missing fields become null-like index keys for uniqueness and why optional fields often need a partial unique index.
Use collation-aware unique indexes for case/diacritic semantics and match query collation to index collation.
Detect dirty duplicates before attempting a unique index build and repair data instead of treating the build error as an outage surprise.
State the scope of an invariant precisely: uniqueness applies to the indexed key combination, not to every business rule around the entity.
This lesson pins MongoDB Community Server
8.3.8 with
mongodb/mongodb-community-server:8.3.8-ubuntu2204-slim
and mongosh 2.10.0. The server is a disposable
standalone published only on loopback
127.0.0.1:27065. Authentication and TLS are
disabled only for this isolated lab. Feature Compatibility
Version (FCV) is observed but never changed. Default read/write
concern and primary read preference apply. Atlas, Search, KMS,
Enterprise Advanced, and paid services are not required. Runtime
output shown as “expected” is documentation-derived because this
generation environment has no Docker/mongod/mongosh runtime.
Index behavior depends on query shape, projection, sort, data
distribution, planner choice, cache state, topology, and patch
version. The labs therefore inspect winningPlan,
totalKeysExamined, totalDocsExamined,
index definitions/sizes, and target query results. Small
fixtures prove semantics, not production latency. Timing
snippets are comparative demonstrations only, not benchmarks.
1. Uniqueness is an invariant, not a lookup optimization
A unique index rejects a write when its index key would
duplicate an existing key in another document. For AtlasMart,
email may be unique only inside one tenant, so the invariant key
is (tenantId,email), not global email. Uniqueness
is enforced during writes; an application-level “check then
insert” has a race window that a unique index closes.
| Case | Unique-key interpretation |
|---|---|
| tenant-a + alice@example.test | Unique tuple for that tenant. |
| tenant-b + alice@example.test | Different tuple because tenant differs; allowed. |
| tenant-a + missing email | The missing indexed field contributes a null-like key component; another same-prefix missing/null key can collide. |
| Collation strength 2 | String comparison is case-insensitive for the indexed collation, so Alice and alice compare equal for uniqueness in that tenant. |
2. Controlled failure: naïve unique index on an optional email
docker rm -f atlasmart-mongo-ch10-l4 2>/dev/null || truedocker volume rm atlasmart-mongo-ch10-l4-data 2>/dev/null || truedocker run -d --name atlasmart-mongo-ch10-l4 \ -p 127.0.0.1:27065:27017 \ -v atlasmart-mongo-ch10-l4-data:/data/db \ mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimmongosh "mongodb://127.0.0.1:27065/atlasmart?directConnection=true" --quiet --eval \'printjson({server:db.version(),hello:db.hello().isWritablePrimary}); printjson(db.getSiblingDB("admin").runCommand({getParameter:1,featureCompatibilityVersion:1}))' const c=db.customers_ch10_l4;c.drop();c.insertMany([ {_id:1,tenantId:"tenant-a",customerId:"C1",username:"Alice",email:"alice@example.test"}, {_id:2,tenantId:"tenant-a",customerId:"C2",username:"Bob"}, {_id:3,tenantId:"tenant-b",customerId:"C3",username:"alice",email:"alice@example.test"}]);printjson(c.find({}).sort({_id:1}).toArray());const c=db.customers_ch10_l4;print(c.createIndex({tenantId:1,email:1},{name:"uq_tenant_email_naive",unique:true}));try{ c.insertOne({_id:4,tenantId:"tenant-a",customerId:"C4",username:"Carol"}); print("UNEXPECTED: second tenant-a document missing email inserted");}catch(e){ printjson({code:e.code,codeName:e.codeName,message:e.message}); }printjson(c.getIndexes());For a unique index, missing indexed fields are represented in the index with null-like key values. Tenant-a already has one document with missing email; another tenant-a document with missing email attempts the same unique tuple. Flexible schema did not remove the invariant—it made the missing-value policy part of index design.
3. Repair optional uniqueness with a partial unique index
If “email must be unique when it is a string, but email is optional” is the real invariant, index only documents that carry an email string. The partial filter changes which documents participate in the unique constraint.
const c=db.customers_ch10_l4;c.dropIndex("uq_tenant_email_naive");print(c.createIndex( {tenantId:1,email:1}, {name:"uq_tenant_email_present",unique:true,partialFilterExpression:{email:{$type:"string"}}}));printjson(c.insertOne({_id:4,tenantId:"tenant-a",customerId:"C4",username:"Carol"}));try{ c.insertOne({_id:5,tenantId:"tenant-a",customerId:"C5",username:"Alicia",email:"alice@example.test"});}catch(e){ printjson({expectedDuplicate:true,code:e.code,message:e.message}); }printjson(c.find({tenantId:"tenant-a"},{_id:0,customerId:1,email:1}).sort({customerId:1}).toArray());The repaired invariant is now: for documents whose email is a BSON string, the pair (tenantId,email) is unique. It does not validate email syntax or guarantee every customer has an email; Chapter 07-style validation handles those separate rules.
4. Collation changes string equality and index eligibility
A collation defines language-aware string comparison rules. With
locale en and strength 2, case differences are
ignored for comparison. The case-insensitive unique index
therefore rejects tenant-a alice because tenant-a
already has Alice. The same username in tenant-c is
allowed because tenantId is part of the key.
const c=db.customers_ch10_l4;print(c.createIndex( {tenantId:1,username:1}, {name:"uq_tenant_username_ci",unique:true,collation:{locale:"en",strength:2}}));try{ c.insertOne({_id:6,tenantId:"tenant-a",customerId:"C6",username:"alice"});}catch(e){ printjson({caseInsensitiveDuplicate:true,code:e.code,message:e.message}); }printjson(c.insertOne({_id:7,tenantId:"tenant-c",customerId:"C7",username:"alice"}));printjson(c.find({tenantId:"tenant-a",username:"alice"},{_id:0,customerId:1,username:1}).collation({locale:"en",strength:2}).explain("executionStats"));To use a non-simple-collation index for string comparisons, the query must specify the same collation. A query that omits or uses a different collation may not use that index for those string comparisons. Collation is semantic, not decorative.
5. Existing duplicates can make a unique index build fail
Adding a unique index to dirty historical data is a migration, not just DDL. The disposable dirty collection contains a duplicate key before the build. MongoDB rejects the build rather than silently deleting a row.
const d=db.customers_ch10_l4_dirty;d.drop();d.insertMany([ {_id:1,tenantId:"tenant-a",email:"dup@example.test"}, {_id:2,tenantId:"tenant-a",email:"dup@example.test"}, {_id:3,tenantId:"tenant-a",email:"ok@example.test"}]);try{ d.createIndex({tenantId:1,email:1},{name:"uq_dirty",unique:true});}catch(e){ printjson({buildRejected:true,code:e.code,message:e.message}); }printjson(d.aggregate([ {$group:{_id:{tenantId:"$tenantId",email:"$email"},n:{$sum:1},ids:{$push:"$_id"}}}, {$match:{n:{$gt:1}}}]).toArray());Inventory duplicates first, define deterministic repair ownership, fix or quarantine them, then build the unique index and verify future duplicate writes fail. On a production replica set/sharded deployment also plan disk headroom, replication lag, commit quorum, and rollback.
6. Topology and application boundary
Unique constraints on sharded collections have additional shard-key restrictions because enforcement must be routable across the cluster. Keep tenant or shard identity in the unique-key design when the business rule is scoped that way, and re-check the exact sharded-index rule before deployment. A unique index also does not replace authorization: tenantId in the key prevents duplicate identifiers, not cross-tenant reads.
A hidden unique index is still maintained and still rejects duplicate keys. Hiding only removes it from the query planner. Lesson 5 uses this property to evaluate read-path removal safely without weakening uniqueness.
7. Verification, cleanup, and production judgment
Verification checklist
- The naïve optional-email unique index allows one tenant-a missing email and rejects a second.
- The partial unique index allows multiple tenant-a documents without string email values.
- The partial index still rejects duplicate tenant/email strings.
- The collation-aware unique index rejects Alice/alice in the same tenant but allows the username in another tenant.
- The dirty unique-index build fails and the duplicate group is observable before repair.
- No application “check first” is presented as equivalent to database-enforced uniqueness.
Production judgment. Unique indexes are one of MongoDB’s strongest data-quality tools because they move race-sensitive identity invariants into the database write path. Their semantics depend on missing/null participation, collation, partial filters, arrays, and topology. Introduce them with data-quality scans and rollback plans, not during an incident. Monitor duplicate-key errors as both client feedback and a possible signal of broken upstream deduplication. Lesson 5 completes the lifecycle: create, observe, hide, safely retire, and deal correctly with the fact that indexes cannot be renamed in place.
docker rm -f atlasmart-mongo-ch10-l4docker volume rm atlasmart-mongo-ch10-l4-dataCheck your understanding
- Why can two documents missing an indexed field conflict under a unique compound index?
- When is a partial unique index useful?
- What does collation strength 2 change in the example?
- Does a hidden unique index stop enforcing uniqueness?
- Why should duplicates be checked before building a new unique index?
Review the answers
1. Missing fields contribute null-like index key values, so the same compound prefix plus missing/null component can duplicate an existing unique key.
2. When the business invariant applies only to documents meeting a condition, such as “email is unique when email is present as a string.”
3. It makes the index comparison case-insensitive, so Alice and alice compare equal for the indexed tenant/username key.
4. No. Hidden indexes remain maintained and unique constraints still apply.
5. The build will reject duplicate keys; finding and repairing them first turns an outage surprise into a controlled migration.
Authoritative references
- MongoDB 8.3 release notes — Current 8.3 baseline and patch-sensitive behavior; re-check before reproduction.
- Indexes overview — Index concepts, names, build considerations, and index-management overview.
- Explain results — IXSCAN/FETCH/COLLSCAN evidence, covered-query plans, keys/documents examined, and execution statistics.
- Measure index use — Using $indexStats and explain evidence instead of intuition to manage indexes.
- mongosh release notes — mongosh version used for chapter commands.
- Unique indexes — Unique index semantics, null/missing behavior, multikey and sharded restrictions.
- Unique indexes and schema validation — Optional-field duplicate-key behavior and partial unique index pattern.
- Collation and index use — String comparison rules and matching operation/index collation semantics.