How to create a unique index in MongoDB (and dedupe first)

A unique index is MongoDB's promise that two documents will never share a value. It is the cleanest way to stop duplicates at the source: enforce it once at the database, and no application bug, race, or rogue script can slip a second one past it. The one thing to know before you run it is that you have to clear the existing duplicates first, because a unique index cannot be built over a collection that already breaks it.

The basic unique index

db.users.createIndex({ email: 1 }, { unique: true })

From now on, any insert or update that would create a second document with an email already in the collection is rejected with a duplicate key error. That is the whole feature, and it is a strong one: uniqueness enforced by the database is worth more than uniqueness checked in application code, because the database has no race conditions between the check and the write.

Unique on a combination of fields

Often the thing that must be unique is not one field but a pair. An order number unique per customer, say, not globally. That is a compound unique index:

db.orders.createIndex({ userId: 1, orderNumber: 1 }, { unique: true })

Now user 1 and user 2 can both have order number 1001, but user 1 cannot have two. The uniqueness applies to the combination, which is usually what identity actually means in your data.

The build that fails: dedupe first

Try to build a unique index on a collection that already has duplicates and it fails, loudly:

E11000 duplicate key error: users index: email_1 dup key: { email: "sam@example.com" }

This is not the index being difficult. It is the index refusing to make a promise the data already breaks. The fix is to clear the duplicates first, then build. Finding and removing them safely is its own job, covered in remove duplicates in MongoDB and the aggregation walk-through. Dedupe, then build, in that order. Reverse it and the build just fails again.

Partial unique: uniqueness for an optional field

Here is a subtle one that catches people. A plain unique index treats a missing field as null, and allows only a single null. So if email is optional, a plain unique index lets exactly one document omit it, and rejects the rest as duplicate nulls. That is almost never what you want.

The fix is a partial unique index that only applies to documents that actually have the field:

db.users.createIndex(
  { email: 1 },
  { unique: true, partialFilterExpression: { email: { $exists: true } } }
)

Now emails are unique among the documents that have one, and any number of documents can leave it out. Partial filters are the flexible, modern tool here; the older sparse option does something similar but is less precise. More on that in sparse and partial indexes.

Or dedupe with a dry run first

The riskiest moment is the cleanup before the build, because it deletes from a live collection. mongoui's duplicate finder groups documents on the key you are about to make unique, shows you the clusters, and runs the removal as a dry run first, with exact before and after counts and one-click undo. Then the unique index goes on cleanly. Its Index view confirms the index built and is being used, so it earns its place in your index budget. Everything runs on your machine, so the documents you are deduping never leave your network. Clear the duplicates, build the index, and let the database keep them gone.