unique and sparse index causes duplicate error

Question

I've got something like this:

var mongoose = require('mongoose');

mongoose.connect('mongodb://...');

var UserSchema = mongoose.Schema({
  name: {
    type: String,
    unique: true
  },
  token: {
    type: String,
    unique: true,
    sparse: true
  }
});

var User = mongoose.model('User', UserSchema);

var peter = new User();

peter.name = 'peter';
peter.token = null;

peter.save(function(err) {
  if (err) console.log(err);
  console.log('done with peter');

  var john = new User();

  john.name = 'john';
  john.token = null;

  john.save(function(err) {
    if (err) console.log(err);
    console.log('done with john');
  });

});

So peter is created first and afterwards john. However when john is saved mongoose returns the following error

{ [MongoError: E11000 duplicate key error index: node-login.users.$token_1  dup key: { :   null }]
  name: 'MongoError',
  err: 'E11000 duplicate key error index: node-login.users.$token_1  dup key: { : null }',
  code: 11000,
  n: 0,
  lastOp: 0,
  connectionId: 4527333,
  ok: 1 }

Code tried on mongolab running mongo v2.0.7 and mongoose v3.5.1.

I thought setting an index to sparse allows for multiple null values. Index is properly set as the "Indexes" tab in mongolab backend shows true for unique and sparse. Any ideas why I get the error?

JohnnyHK · Accepted Answer · 2012-12-16T19:11:31.510

5

A sparse index only indexes the field if a document has that field. Whether the field is set to null or some other value doesn't matter, it's still checked for uniqueness if present.

So in your case, omit setting the token field at all instead of setting it to null.

edited Dec 16 '12 at 19:11

answered Dec 16 '12 at 18:59

JohnnyHK

305,182
66
621
471

I need the `token` for user registration. User signs up and email with token is sent. User clicks on link with token. My app sees the token and verifies it. After verification I would like to set the token to `null`. So in the end I will have multiple users with unique tokens and multiple with `null` tokens. How would you solve this problem? – zemirco Dec 16 '12 at 21:18
1

If you want to keep the unique sparse index, don't set `token` to `null`, [`$unset`](http://docs.mongodb.org/manual/reference/operators/#_S_unset) it instead to remove it. – JohnnyHK Dec 16 '12 at 21:27

score 2 · Answer 2 · edited May 23 '17 at 12:25

2

In JavaScript setting the value to null will not delete that property, rather it sets the value as null. In your case you should delete the token property.

delete john.token;  // In place of John.token = null

Do the same for peter.

Refer more on delete: Remove a property from a JavaScript object

edited May 23 '17 at 12:25

Community

1
1

answered Jun 03 '15 at 14:27

dCoder

374
3
14

unique and sparse index causes duplicate error

2 Answers2