0

I have a model

var ReferenceSchema = mongoose.Schema({
    ownerId:{
        type:Number,
        required:true,
        index: true
    },
    boardId:{
        type:Number,
        index:true
    },
    boardCommentId:{
        type:Number,
        index: true
    },
    postId:{
        type:Number,
        index:true
    },
    postCommentId:{
        type:Number,
        index: true
    }
});

that stores different previously founded references. It could be references of different types and should be combination of different values:
ownerId - always
boardId + boardCommentId OR postId + postCommentId

And I have this combined indexes:

ReferenceSchema.index({ ownerId: 1, postId: 1 }, { unique: true,sparse: true });
ReferenceSchema.index({ postId: 1, postCommentId: 1 }, { unique: true,sparse: true });
ReferenceSchema.index({ boardId: 1, boardCommentId: 1 }, { unique: true,sparse: true });

Problem is when I'm trying to add new document, I have an error E11000 duplicate key error index: db.references.$ownerId_1_postId_1 dup key: { : 33390409, : null } even with sparse:true. Am I using sparse incorrectly or I need another approach with compound indexes and sparse?

BadVolt
  • 617
  • 1
  • 7
  • 19
  • Can you check in your db there is any document with ownerId 33390409 and postId null ? I think you are violating Unique contract not sparse – somallg Dec 10 '15 at 09:43
  • There is. But main problem is this field is not existing in current setup. Only ownerId, boardId and boardCommentId – BadVolt Dec 10 '15 at 09:46
  • 1
    Because you define a compound index of ownerId and postId the index key will be something like this { : 33390409, : null }, so next time you insert document with ownerId 33390409 its will fail. You might need to remove this index `{ ownerId: 1, postId: 1 }` – somallg Dec 10 '15 at 09:51
  • That's not an option. I want to make sure if there's a postId it must be unique to ownerId – BadVolt Dec 10 '15 at 09:52
  • 1
    @BadVolt If that is not an option, you have a non-working system. The problem surely is the data modeling here. It seems like a variation of [overembedding](http://blog.mahlberg.io/blog/2015/11/05/data-modelling-for-mongodb/). You have a one to many relation here, and you reference from the "one" side, which may run into the BSON size limit of 16MB. You should reference the user from the "many" side, presumably boards and posts. – Markus W Mahlberg Dec 10 '15 at 11:03
  • Probably you are right, it's complicated. I changed my compound indexes to check for another fields, such as fromId, date, ownerId (not displayed here) to check for unique. Working fine, but I still can't understand why checking optional fields if they are not presented. Same problem [here](http://stackoverflow.com/questions/28183109/dealing-with-mongodb-unique-sparse-compound-indexes) – BadVolt Dec 10 '15 at 11:14

0 Answers0