E11000 duplicate key error collection: archiwiz.clients index: cnic_1 dup key: { cnic: null }

Question

Why do I get this error?When I don't have the Cnic field in my DB anymore.

Here is my schema....

      const mongoose = require('mongoose')
      const uuidv1 = require("uuidv1")
      const crypto = require("crypto")
      const { ObjectId } = mongoose.Schema

  const clientSchema = new mongoose.Schema({
   name:{
    type:String,
    trim: true,
    required: true,
    // match:[
    //     new RegExp('^[a-z]+$', 'i'),
    //     'Name Should have alphabets'
    // ]
},

phone:{
    type: String,
    trim: true,
    required: true,
    unique: true

},
email:{
    type: String,
    trim: true,
    required: true,
    unique:true
},
hashed_password:{
    type: String,
    required: true

},
salt:String,
created:{
    type: Date,
    default: Date.now
},
createdBy:{
    type: ObjectId,
    ref: "Admin"
},
updated: Date

})


clientSchema.virtual('password')
.set(function(password){
this._password = password
// generate a timestamp
this.salt = uuidv1()
// encrypt password
this.hashed_password = this.encryptPassword(password)
})
.get(function(){
return this._password
})

clientSchema.methods = {

authenticate: function(plainText){
    return this.encryptPassword(plainText) === this.hashed_password
},

encryptPassword: function(password){
    if(!password) return "";
    try{
         return crypto
                .createHmac("sha1", this.salt)
                .update(password)
                .digest('hex');
    }catch (err){
        return ""
    }
}
  }
module.exports = mongoose.model("Client", clientSchema)

I have made some changes , before this I had the Cnic field which was Unique...I have deleted that field.When I create a first user it gets created successfully but when I create a second user it gives the error above in the title...What is happening and how to solve it..?

------------------------SOLUTION----------------------------------------

I dropped a collection in my DB and it worked for me. Detailed answer is available in the comment section provided my @Scott Gnile

Answer 1

What happens is that MongoDB does not store the schema in the collection as if it were a table in a SQL Database, but it does store the indexes.

When you made the field unique, MongoDb created an index on that collection for that field.

So after inserting one document without that field, an entry in the index list goes as null. When you insert another document without that field, you are trying to insert another document with the same value null in the collection, and it fails because that field is supposed to be unique.

I've done the step by step and took a screenshot for you, see it below:

If you had a lot of data in that collection, dropping it wouldn't be an option, so you could drop the index, and it would work fine.

1. First, you list the indexes of such collection
2. Then, you identify which index to drop
3. You drop the index
4. Then you insert the same document that previously failed

See it below:

But there's an intermediate solution if you want to have an optional field that is unique across documents. This means that such a field is not present all the time, but it has to be unique when it is.

The solution for that is to create a sparse index. In the image below, you'll see that:

1. A new index (sparse and unique) is created on the same collection
2. The index creation happens successfully, which wouldn't happen if there were duplicate values for the unique field.
3. A document without the unique field is inserted with no issues
4. A document is inserted with a value for the unique field with success
5. Another document is attempted to be inserted in the collection with the same value for the unique field as the previous document, and as expected, it fails
6. Then, finally, another document is successfully inserted with a different value for that field.