2

I'm trying to add a username field to documents in a 'users' collection, and I'd like it to be a unique index. (So far, we've been using email addresses for login but we'd like to add a username field as well.) However, running db.users.ensureIndex({username:1},{unique:true}) fails because mongo considers all the unset usernames to be duplicates and therefore not unique. Anybody know how to get around this?

Show the current users and username if they have one:

> db.users.find({},{_id:0,display_name:1,username:1})
    { "display_name" : "james" }
    { "display_name" : "sammy", "username" : "sammy" }
    { "display_name" : "patrick" }

Attempt to make the 'username' field a unique index:

> db.users.ensureIndex({username:1},{unique:true})
    {
        "err" : "E11000 duplicate key error index: blend-db1.users.$username_1  dup key: { : null }",
        "code" : 11000,
        "n" : 0,
        "connectionId" : 272,
        "ok" : 1
    }

It doesn't work because both james and sammy have username:null.

Let's set patrick's username to 'patrick' to eliminate the duplicate null value.

> db.users.update({display_name: 'patrick'}, { $set: {username: 'patrick'}});
> db.users.ensureIndex({username:1},{unique:true})
> db.users.getIndexes()
    [
        {
            "v" : 1,
            "key" : {
                "_id" : 1
            },
            "ns" : "blend-db1.users",
            "name" : "_id_"
        },
        {
            "v" : 1,
            "key" : {
                "username" : 1
            },
            "unique" : true,
            "ns" : "blend-db1.users",
            "name" : "username_1"
        }
    ]

Now it works!

To clarify the question, what I'd like is to be able to make username a unique index without having to worry about all the documents that have username still set to null.

calvinf
  • 3,754
  • 3
  • 28
  • 41
Patrick Estabrook
  • 125
  • 1
  • 9

1 Answers1

2

Try creating a unique sparse index:

db.users.ensureIndex({username:1},{unique:true,sparse:true})

As per the docs:

You can combine the sparse index option with the unique indexes option so that mongod will reject documents that have duplicate values for a field, but that ignore documents that do not have the key.

Although this only works for documents which don't have the field, as opposed to documents that do have the field, but where the field has a null value.

robertklep
  • 198,204
  • 35
  • 394
  • 381
  • I need to recheck this but I think the field not existing and being null is the same in a sparse index, I can't totally remember though – Sammaye Jun 28 '13 at 20:24
  • @Sammaye I could be mistaken, I based that part on [this answer](http://stackoverflow.com/a/8608585/893780), although it's pretty old and things might have changed since :) – robertklep Jun 28 '13 at 20:30
  • That answer could be right, I just remember something along these lines about sparse; worth checking out – Sammaye Jun 28 '13 at 20:43
  • does it work as case insensitive?. I think no then there is no use of it to create unique usernames. – Rahul Tailwal Jun 23 '14 at 09:46
  • @RahulTailwal the uniqueness check is case-sensitive, so usernames `foo` and `FOO` are considered to be different and won't trigger a uniqueness violation. You could upper- or lowercase usernames before saving them to the database if you want case-insensitivity. – robertklep Jun 23 '14 at 09:51