azureazure-cosmosdbmongo-shellazure-cosmosdb-mongoapiazure-cosmosdb-mongovcore

How can I reliably create unique indexes in Cosmos DB for MongoDB?


I have been having issues creating unique indexes in Cosmos DB for MongoDB. Commands that work on MongoDB sometimes work on Cosmos DB for MongoDB, but sometimes they result in errors. How can I know when I am allowed to create unique indexes? I'd like to know without blindly trying it and waiting for error messages to appear.

I am trying to add a unique index to an existing collection that already has documents in it. Here are some mongosh commands to do this:

db.createCollection("testCollection")
db.testCollection.insertOne({someField: "value"})
db.testCollection.createIndex({someField: 1}, {unique: true})

The commands above succeed on MongoDB and Cosmos DB for MongoDB (vCore). However, on Cosmos DB for MongoDB (RU), the final command results in this error message:

Cannot create unique index when collection contains documents

My next attempt was to create a new collection and create the unique indexes first, before adding any documents to the collection. Here are some mongosh commands to do this:

db.createCollection("anotherCollection")
db.anotherCollection.createIndex({someField: 1}, {unique: true})

The commands above succeed on MongoDB, CosmosDB for MongoDB (vCore), and Cosmos DB for MongoDB (RU) with periodic backup. However, on Cosmos DB for MongoDB (RU) with continuous backup (point in time restore), the final command results in this error message:

Error=13, Details='Response status code does not indicate success: Forbidden (403); Substatus: 0; ActivityId: ; Reason: (Message: {"Errors":["The unique index cannot be modified. To change the unique index, remove the collection and re-create a new one."]}


Solution

  • I eventually found that Microsoft's Azure documentation confirms the behaviors described in the question.

    The Unique Keys page says:

    To set a unique key for an existing container, create a new container with the unique key constraint. Use the appropriate data migration tool to move the data from the existing container to the new container.

    The Continuous Backup page says:

    Azure Cosmos DB for MongoDB accounts with continuous backup do not support creating a unique index for an existing collection. For such an account, unique indexes must be created along with their collection; this is done using the create collection extension commands.

    To summarize, if you want to create a unique index on a collection that already has documents in it, your options are limited depending on the variant of CosmosDB you are running:

    Cosmos DB for MongoDB (RU) with continuous backup (point in time restore)
    Use the CreateCollection extension command to create a new collection and the unique indexes at the same time. For example:

    db.runCommand({
      customAction: "CreateCollection",
      collection: "anotherCollection",
      indexes: [{key: {someField: 1}, name: "someField_1", unique: true}]
    })
    

    Note: If you are using mongorestore to copy the existing documents to the new collection, you may get errors when it tries to create the unique indexes if they don't exactly match the ones you manually created. If you have created all (unique and non-unique) indexes already, you might consider using the --noIndexRestore option.

    Cosmos DB for MongoDB (RU) with periodic backup
    Use the previous method, or you can create a new collection and then use the normal MongoDB createIndex command to create unique indexes. For example:

    db.createCollection("anotherCollection")
    db.anotherCollection.createIndex({someField: 1}, {unique: true})
    

    Cosmos DB for MongoDB (vCore)
    I did some testing with MongoDB 6.0 interface, and it appears that you can use the normal MongoDB createIndex command to create unique indexes, even after there are documents in the collection.

    db.testCollection.createIndex({someField: 1}, {unique: true})