2

Is it possible to make a compound index, where one of the fields have a fixed value?

Let's say I want to avoid users using the same e-mail for different accounts, but just for regular user accounts, and I want to allow admins to use the mail in as many places as they want, and even have a regular user account and an administrative account using the same e-mail

User.index({ username: 1, email: 1 }, { unique: true })

Is not useful, since it will not allow admins to reuse the email. Is it possible to do something like?

User.index({ role: "regular_user", username 1, email: 1}, { unique: true });
Luis Sieira
  • 29,926
  • 3
  • 31
  • 53
  • I posted an answer on indexes, this may help you decide if its good for you to create a compound index, with one of the fields having a fixed value. http://stackoverflow.com/questions/33545339/how-does-the-order-of-compound-indexes-matter-in-mongodb-performance-wise/33546159#answer-33546159 – Abdullah Rasheed Nov 06 '15 at 14:54
  • Good answer. Actually, in my case, I'm improving a schema inheritance wrapper for mongoose (https://github.com/briankircho/mongoose-schema-extend), and I have to change all the unique keys to discriminator/key unique indexes, but only for those values of the discriminator that define such field as unique. The example above is the easiest I could find to explain it. – Luis Sieira Nov 06 '15 at 15:00
  • How did things work out for you? – Abdullah Rasheed Nov 08 '15 at 16:47
  • I have an idea based on your answer. I'll let you know (and accept your answer) tomorrow – Luis Sieira Nov 08 '15 at 16:56
  • I accepted your answer, since it is useful for the question as I asked it, and may be useful to others. I'm still stuck with this, anyways :-p – Luis Sieira Nov 09 '15 at 10:44
  • I'm glad I could help a little bit. Sorry to hear that :p – Abdullah Rasheed Nov 09 '15 at 13:18
  • I did it the hackiest way ever, using a discriminator field with a 2dsphere index... I'll show you as soon as I upload the package to npm... I'm resurrecting a dead npm module (no activity in three years), and I didn't choose a good name xD – Luis Sieira Nov 09 '15 at 15:36
  • looking forward to seeing it – Abdullah Rasheed Nov 09 '15 at 15:36

2 Answers2

2

Luis,

In regards to the example that you gave. If you create a unique compound index, individual keys can have the same values, but the combination of values across the keys that exist in the index entry can only appear once. So if we had a unique index on {"username" : 1, "role" : 1}. The following inserts would be legal:

> db.users.insert({"username" : "Luis Sieira"})
> db.users.insert({"username" : "Luis Sieira", "role" : "regular"})
> db.users.insert({"username" : "Luis Sieira", "role" : "admin"})

If you tried to insert a second copy of any of the above documents, you would cause a duplicate key exception.

Your Scenarios

I think that if you added an allowance field to your schema. When you do inserts for admins for new accounts. You can add a different value for their admin allowance. If you added unique index for {"username":1,"email":1, "allowance" : 1}

You could make the following inserts, legally:

>db.users.insert({"username" : "inspired","email": "i@so.com", "allowance": 0})
>db.users.insert({"username" : "inspired","email": "i@so.com", "allowance": 1})
>db.users.insert({"username" : "inspired","email": "i@so.com", "allowance": 2})
>db.users.insert({"username" : "inspired","email": "i@so.com", "allowance": 3})

Of course, you'll have to handle certain logic from the client, but this will allow you to use an allowance code of 0 for regular accounts and then allow you to save a higher allowance code (incrementing it or adding custom value for it) each time an admin creates another account.

I hope this offers some direction with using unique compound indexes.

Abdullah Rasheed
  • 3,562
  • 4
  • 33
  • 51
1

You are on the right track. First things first, if you define an index with the role like this

User.index({role: 1, username: 1, email: 1}, { unique: true });

Mongo will use null for documents that do not specify the role field. If you insert an user without specifying the role and try to add it again, you will get an error because the three fields already exist in the database. So you can use this to your advantage by not including a role (or you can use a predefined value for better reading, like you proposed as regular_user).

Now, the tricky part is forcing the index to permit admins to bypass the uniqueness constraint. The best solution would be to generate a some hash and add it to the role. So, if you just add admins with roles like admin_user, you won't bypass the constraint. Meanwhile, using a role like admin_user_635646 (always with varying suffix) will allow you to insert the same admin multiple times.

cenouro
  • 715
  • 3
  • 15