CODEWITHSUNDEEP
node.jsmongodbmongoose
weichao
weichao

Reputation: 3411

mongodb compound index unique if A and B both are same

There are many similar questions but none of them solved my question :(

How to index multiple unique data fields in a single document in Mongoose?

MongoDb create unique index on a field not in all documents

Make combination of two fields unique in my collection

My item schema is here

{
    NamespaceId: {
        type: ObjectId,
        required: true
    },
    Pattern: {
        type: String,
        required: true,
        default: ''
    },
    Key: {
        type: String,
        required: true,
    },
    Value: {
        type: String,
        required: true
    },
    CreatedBy: {
        type: String,
        required: true
    },
    Comments: {
        type: String,
        required: false,
        default: ''
    },
    IsBanned: {
        type: Boolean,
        required: true,
        default: false
    },
    Status: {
        type: String,
        required: true
    }
}

Now, I have following documents in my collections

{
    NamespaceId: 123,
    Key: 'first key',
    Value: 'first value',
    W: '1'
},
{
    NamespaceId: 456,
    Key: 'second key',
    Value: 'second value',
    W: 2
},
{
    NamespaceId: 789,
    Key: 'third key',
    Value: 'third value',
    W: '3'
}

I want NamespaceId and Key and Value are unique. So I create a compounded unique for above collection.

ItemSchema.index({
    'Key': 1,
    'Value': 1,
    'NamespaceId': 1
},{unique: true })

From my thoughts, following document can insert into because there no same indexes exist in collection.

{
    NamespaceId: 123,
    Key: 'second key',
    Value: 'first value'
}

But mongoose(nodejs sdk of mongodb) tell me:

"BulkWriteError: E11000 duplicate key error collection: configservice_test.configservice_items_tests index: NamespaceId_1 dup key: { NamespaceId: ObjectId('5f2aaabd4440bb566487cf70') }"

Mongodb found that NamespaceId has already exists so it just throw an exception without checking left Key and Value.

Which are different from Mysql on index concept.

My workaround is to create a new single unique column which value is composed by NamespaceId, Key, Value like 123_first_key_first_value.

Can my needs be achieved by Mongoose?

Upvotes: 1

Views: 2418

Answers (1)

weichao
weichao

Reputation: 3411

Answer by myself.

You Must create Index before create model

whole half day to solve it, tired :(

The following codes cause bugs

this.model = mongoose.model(item_collection, ItemSchema)
ItemSchema.index({
    'Key': 1,
    'Value': 1,
    'NamespaceId': 1
},{unique: true ,background: true})

Instead of the following not

ItemSchema.index({
    'Key': 1,
    'Value': 1,
    'NamespaceId': 1
},{unique: true ,background: true})
this.model = mongoose.model(item_collection, ItemSchema)

Upvotes: 2

Related Questions