index-unique.txt

.. _index-type-unique:

==============
Unique Indexes
==============

.. default-domain:: mongodb

.. facet::
   :name: programming_language
   :values: shell 

.. facet::
   :name: genre 
   :values: reference

.. meta:: 
   :description: Use a unique index to ensure indexed fields do not store duplicate values.

.. contents:: On this page
   :local:
   :backlinks: none
   :depth: 1
   :class: singlecol

A unique index ensures that the indexed fields do not store duplicate
values. A unique index on a single field ensures that a value appears 
at most once for a given field. A unique compound index ensures that 
any given *combination* of the index key values only appears at most 
once. By default, MongoDB creates a unique index on the :ref:`_id 
<document-id-field>` field during the creation of a collection.

.. |page-topic| replace:: :atlas:`create and manage unique indexes in the UI </atlas-ui/indexes>`

.. cta-banner::
   :url: https://www.mongodb.com/docs/atlas/atlas-ui/indexes/
   :icon: Cloud
   
   .. include:: /includes/fact-atlas-compatible.rst

.. _index-unique-create:

Create a Unique Index
---------------------

To create a unique index, use the :method:`db.collection.createIndex()`
method with the ``unique`` option set to ``true``. 

.. code-block:: javascript

   db.collection.createIndex( <key and index type specification>, { unique: true } )


.. _index-unique-index:

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
:binary:`~bin.mongosh`:

.. code-block:: javascript

   db.members.createIndex( { "user_id": 1 }, { unique: true } )

.. _index-unique-compound-index:

Unique Compound Index
~~~~~~~~~~~~~~~~~~~~~

You can also enforce a unique constraint on :ref:`compound indexes
<index-type-compound>`. A unique :ref:`compound index 
<index-type-compound>` enforces 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 :binary:`~bin.mongosh`:

.. code-block:: javascript

   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:

.. code-block:: javascript

   { _id: 1, a: [ { loc: "A", qty: 5 }, { qty: 10 } ] }

Create a unique compound :ref:`multikey <index-type-multikey>` index
on ``a.loc`` and ``a.qty``:

.. code-block:: javascript

   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:

.. code-block:: javascript

   db.collection.insertMany( [
      { _id: 2, a: [ { loc: "A" }, { qty: 5 } ] },
      { _id: 3, a: [ { loc: "A", qty: 10 } ] }
   ] )

.. seealso::

   - :ref:`unique-separate-documents`
   - :ref:`unique-index-and-missing-field`

Behavior
--------

.. _unique-index-restrictions:

Restrictions
~~~~~~~~~~~~~

MongoDB cannot create a :ref:`unique index <index-type-unique>` 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 :ref:`hashed
index <index-type-hashed>`.

Building Unique Index on Replica Sets and Sharded Clusters
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For replica sets and sharded clusters, using a :ref:`rolling procedure
<index-build-on-replica-sets>` 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, to build your 
unique index on the collection you must either:

- Run ``db.collection.createIndex()`` on the primary for a
  replica set
- Run ``db.collection.createIndex()`` on the :binary:`~bin.mongos` 
  for a sharded cluster
 
.. _unique-separate-documents:

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
:ref:`multikey <index-type-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:

.. code-block:: javascript

   { _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``:

.. code-block:: javascript

   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 }``.

.. code-block:: javascript

   db.collection.insertOne( { _id: 4, a: [ { loc: "B" }, { loc: "B" } ] } )

.. _unique-index-and-missing-field:

Missing Document Field in a Unique Single-Field Index
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If a document has a ``null`` or missing value for the indexed field in a unique
single-field index, the index stores a ``null`` value for that document.
Because of the unique constraint, a single-field unique index can only
contain one document that contains a ``null`` value in its index entry. If there is
more than one document with a ``null`` value in its index entry, the index
build fails with a duplicate key error.

For example, a collection has a unique single-field index on ``x``:

.. code-block:: javascript

   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``:

.. code-block:: javascript

   db.collection.insertOne( { y: 1 } )

However, you cannot insert a document without the field ``x`` if the
collection already contains a document missing the field ``x``:

.. code-block:: javascript

   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``:

.. code-block:: javascript

   WriteResult({
      "nInserted" : 0,
      "writeError" : {
         "code" : 11000,
         "errmsg" : "E11000 duplicate key error index: test.collection.$a.b_1 dup key: { : null }"
      }
   })

.. _unique-partial-indexes:

Missing Document Fields in a Unique Compound Index
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If a document has a ``null`` or missing value for one or more indexed
fields in a unique compound index, the index stores a null value for
each ``null`` or missing field in the document's index entry. Because of
the unique constraint, a unique compound index only permits one document
that has a ``null`` value for all indexed fields in an index entry. If
there is more than one index entry with a ``null`` value for all indexed
fields, the index build fails with a duplicate key error. MongoDB
permits multiple documents with missing fields in unique compound
indexes as long as each index entry is unique. 

For example, a collection ``students`` has a unique compound index on fields
``name``, ``age``, and ``grade``:

.. code-block:: javascript

   db.students.createIndex( 
      { 
         "name": 1, 
         "age": -1, 
         "grade": 1 
      }, 
      { unique: true } 
   )

If the collection does not already contain identical documents, the
unique compound index allows the insertion of the following documents
that are all missing the ``grade`` field. 

.. code-block:: javascript
   
   db.students.insertMany(
      { "name": "Meredith", "age": 12 }, 
      { "name": "Olivia", "age": 11 }, 
      { "name": "Benjamin" } 
   )

However, you cannot insert a document that has the same index key (value
for ``name``, ``age``, and ``grade``) as another document in the
collection. 

.. code-block:: javascript
   
   db.students.insertOne( { name: "Meredith", age: 12 } )

The operation fails to insert the document because of the violation of
the unique constraint on the values of the fields ``name``, ``age``, and ``grade``:

.. code-block:: javascript

   WriteResult({
      "nInserted" : 0,
      "writeError" : {
         "code" : 11000, 
         "errmsg" :
            "E11000 duplicate key error collection: test.students 
            index: name_1_age_-1_grade_1 
            dup key: { name: "Meredith", age: 12, grade: null }
      }
   } )

You also cannot insert a document that is unique but shares an index
key with an existing index entry. 

.. code-block:: javascript
   
   db.students.insertOne( { name: "Olivia", "age": 11, "favorite color": "red"} )
   
The operation fails to insert the document because of the violation of
the unique constraint on the values of the fields ``name``, ``age``, and
``grade``:

.. code-block:: javascript

   WriteResult({
      "nInserted" : 0,
      "writeError" : {
         "code" : 11000, 
         "errmsg" :
            "E11000 duplicate key error collection: test.students 
            index: name_1_age_-1_grade_1 
            dup key: { name: "Olivia", age: 11, grade: 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 :ref:`unique constraint
<index-type-unique>`, 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
:ref:`partial-index-with-unique-constraints`.

.. _sharded-clusters-unique-indexes:

Sharded Clusters and Unique Indexes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You cannot specify a unique constraint on a :ref:`hashed index
<index-type-hashed>`.

For a ranged sharded collection, only the following indexes can be
:ref:`unique <index-type-unique>`:

- the index on the shard key

- a :term:`compound index` where the shard key is a :ref:`prefix
  <compound-index-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. 

.. include:: /includes/sharding/shard-collection-uniqueness-enforcement-note.rst

.. include:: /includes/sharding/sharding-unique-index-constraints.rst

To maintain uniqueness on a field that is not your shard key, 
see :ref:`shard-key-arbitrary-uniqueness`. 

Sparse and Non-Sparse Unique Indexes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. include:: /includes/fact-5.0-sparse-unique-index-updates.rst

Basic and Unique Indexes With Duplicate Key Patterns
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Starting in MongoDB 5.0, basic and unique indexes can exist with the
same :ref:`key pattern <key_patterns>`. 

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.

.. code-block:: javascript

   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 }``.

.. code-block:: javascript

   db.scoreHistory.createIndex( { score : 1 }, { name: "unique_index", unique: true } )

Try to insert a duplicate ``score`` document that fails because of
the unique index.

.. code-block:: javascript

   db.scoreHistory.insert( { score : 3 } )

.. toctree::
   :titlesonly: 

   Convert to Unique </core/index-unique/convert-to-unique>