CODEWITHSUNDEEP
mongodbindexing
Afshin Mehrabani
Afshin Mehrabani

Reputation: 34929

Add unique sparse index to an existing database

I have a database and now I want to add a unique and sparse index to my existing collection. The problem is that I'm getting this error from MongoDB:

{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 3,
        "ok" : 0,
        "errmsg" : "E11000 duplicate key error index: dbname.posts.$ref.origin_1_user_1  dup key: { : null, : ObjectId('54df4f8f93a640bd16000001') }",
        "code" : 11000
}

As far as I know, sparse index is used for columns that might have null values, so that's the reason I used sparse option:

db.posts.ensureIndex({ "ref.origin": 1, "user": 1 },  {  unique: true, sparse: true })

What's the problem with my createIndex command?

Upvotes: 1

Views: 59

Answers (1)

mnemosyn
mnemosyn

Reputation: 46301

Well, if the 2-tuple ('ref.origin', 'user') isn't unique, you got a unique key violation. In this case, the tuple's value is { null, ObjectId('54df4f8f93a640bd16000001') }.

The fact that the index is sparse will matter only if the entire set of fields is absent, i.e. even { null, null } tuples won't be ignored, but their uniqueness is ensured. Both fields, i.e. both ref.origin and user must be unset for this to work.

Upvotes: 2

Related Questions