Unique Indexes
On this page
A unique index ensures that the indexed fields do not store duplicate values; i.e. enforces uniqueness for the indexed fields. By default, MongoDB creates a unique index on the _id field during the creation of a collection.
Note
New Internal Format
Starting in MongoDB 4.2, for featureCompatibilityVersion (fCV) of 4.2 (or greater), MongoDB uses a new internal format for unique indexes that is incompatible with earlier MongoDB versions. The new format applies to both existing unique indexes as well as newly created/rebuilt unique indexes.
Create a Unique Index
To create a unique index, use the db.collection.createIndex()
method with the unique
option set to true
.
db.collection.createIndex( <key and index type specification>, { unique: true } )
Unique Index on a Single Field
For example, to create a unique index on the user_id
field of the
members
collection, use the following operation in
mongosh
:
db.members.createIndex( { "user_id": 1 }, { unique: true } )
Unique Compound Index
You can also enforce a unique constraint on compound indexes. If you use the unique constraint on a compound index, then MongoDB will enforce uniqueness on the combination of the index key values.
For example, to create a unique index on groupNumber
, lastname
,
and firstname
fields of the members
collection, use the
following operation in mongosh
:
db.members.createIndex( { groupNumber: 1, lastname: 1, firstname: 1 }, { unique: true } )
The created index enforces uniqueness for the combination of
groupNumber
, lastname
, and firstname
values.
For another example, consider a collection with the following document:
{ _id: 1, a: [ { loc: "A", qty: 5 }, { qty: 10 } ] }
Create a unique compound multikey index
on a.loc
and a.qty
:
db.collection.createIndex( { "a.loc": 1, "a.qty": 1 }, { unique: true } )
The unique index permits the insertion of the following documents into
the collection since the index enforces uniqueness for the
combination of a.loc
and a.qty
values:
db.collection.insertMany( [ { _id: 2, a: [ { loc: "A" }, { qty: 5 } ] }, { _id: 3, a: [ { loc: "A", qty: 10 } ] } ] )
Behavior
Restrictions
MongoDB cannot create a unique index on the specified index field(s) if the collection already contains data that would violate the unique constraint for the index.
You may not specify a unique constraint on a hashed index.
Building Unique Index on Replica Sets and Sharded Clusters
For replica sets and sharded clusters, using a rolling procedure to create a unique index requires that you stop all writes to the collection during the procedure. If you cannot stop all writes to the collection during the procedure, do not use the rolling procedure. Instead, build your unique index on the collection by:
issuing
db.collection.createIndex()
on the primary for a replica set, orissuing
db.collection.createIndex()
on themongos
for a sharded cluster.
Unique Constraint Across Separate Documents
The unique constraint applies to separate documents in the collection. That is, the unique index prevents separate documents from having the same value for the indexed key.
Because the constraint applies to separate documents, for a unique multikey index, a document may have array elements that result in repeating index key values as long as the index key values for that document do not duplicate those of another document. In this case, the repeated index entry is inserted into the index only once.
For example, consider a collection with the following documents:
{ _id: 1, a: [ { loc: "A", qty: 5 }, { qty: 10 } ] } { _id: 2, a: [ { loc: "A" }, { qty: 5 } ] } { _id: 3, a: [ { loc: "A", qty: 10 } ] }
Create a unique compound multikey index on a.loc
and a.qty
:
db.collection.createIndex( { "a.loc": 1, "a.qty": 1 }, { unique: true } )
The unique index permits the insertion of the following document into
the collection if no other document in the collection has an index key
value of { "a.loc": "B", "a.qty": null }
.
db.collection.insertOne( { _id: 4, a: [ { loc: "B" }, { loc: "B" } ] } )
Unique Index and Missing Field
If a document does not have a value for the indexed field in a unique index, the index will store a null value for this document. Because of the unique constraint, MongoDB will only permit one document that lacks the indexed field. If there is more than one document without a value for the indexed field or is missing the indexed field, the index build will fail with a duplicate key error.
For example, a collection has a unique index on x
:
db.collection.createIndex( { "x": 1 }, { unique: true } )
The unique index allows the insertion of a document without the field
x
if the collection does not already contain a document missing the
field x
:
db.collection.insertOne( { y: 1 } )
However, the unique index errors on the insertion of a document without
the field x
if the collection already contains a document missing
the field x
:
db.collection.insertOne( { z: 1 } )
The operation fails to insert the document because of the violation of
the unique constraint on the value of the field x
:
WriteResult({ "nInserted" : 0, "writeError" : { "code" : 11000, "errmsg" : "E11000 duplicate key error index: test.collection.$a.b_1 dup key: { : null }" } })
Unique Partial Indexes
Partial indexes only index the documents in a collection that meet a
specified filter expression. If you specify both the
partialFilterExpression
and a unique constraint, the unique constraint only applies to the
documents that meet the filter expression.
A partial index with a unique constraint does not prevent the insertion of documents that do not meet the unique constraint if the documents do not meet the filter criteria. For an example, see Partial Index with Unique Constraint.
Sharded Clusters and Unique Indexes
You cannot specify a unique constraint on a hashed index.
For a ranged sharded collection, only the following indexes can be unique:
the index on the shard key
a compound index where the shard key is a prefix
the default
_id
index; however, the_id
index only enforces the uniqueness constraint per shard if the_id
field is not the shard key or the prefix of the shard key.Important
Uniqueness and the _id Index
If the
_id
field is not the shard key or the prefix of the shard key,_id
index only enforces the uniqueness constraint per shard and not across shards.For example, consider a sharded collection (with shard key
{x: 1}
) that spans two shards A and B. Because the_id
key is not part of the shard key, the collection could have a document with_id
value1
in shard A and another document with_id
value1
in shard B.If the
_id
field is not the shard key nor the prefix of the shard key, MongoDB expects applications to enforce the uniqueness of the_id
values across the shards.
The unique index constraints mean that:
For a to-be-sharded collection, you cannot shard the collection if the collection has other unique indexes.
For an already-sharded collection, you cannot create unique indexes on other fields.
Sparse and Non-Sparse Unique Indexes
Starting in MongoDB 5.0, unique sparse and unique non-sparse indexes with the same key pattern can exist on a single collection.
Unique and Sparse Index Creation
This example creates multiple indexes with the same key pattern and
different sparse
options:
db.scoreHistory.createIndex( { score : 1 }, { name: "unique_index", unique: true } ) db.scoreHistory.createIndex( { score : 1 }, { name: "unique_sparse_index", unique: true, sparse: true } )
Basic and Sparse Index Creation
You can also create basic indexes with the same key pattern with and without the sparse option:
db.scoreHistory.createIndex( { score : 1 }, { name: "sparse_index", sparse: true } ) db.scoreHistory.createIndex( { score : 1 }, { name: "basic_index" } )
Basic and Unique Indexes With Duplicate Key Patterns
Starting in MongoDB 5.0, basic and unique indexes can exist with the same key pattern.
This duplication in key patterns allows for adding a unique index to already indexed fields.
In this example:
Create a basic index with the key pattern { score : 1 }
and insert
three documents.
db.scoreHistory.createIndex( { score : 1 }, { name: "basic_index" } ) db.scoreHistory.insert( { score : 1 } ) db.scoreHistory.insert( { score : 2 } ) db.scoreHistory.insert( { score : 3 } )
Create a unique index with the same key pattern { score : 1 }
.
db.scoreHistory.createIndex( { score : 1 }, { name: "unique_index", unique: true } )
Try to insert a duplicate score
document that fails because of
the unique index.
db.scoreHistory.insert( { score : 3 } )