CODEWITHSUNDEEP
mongodbmongoose
Luis Sieira
Luis Sieira

Reputation: 31612

mongoose compound index for fixed value field

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 });

Upvotes: 2

Views: 606

Answers (2)

Abdullah Rasheed
Abdullah Rasheed

Reputation: 3752

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": "[email protected]", "allowance": 0})
>db.users.insert({"username" : "inspired","email": "[email protected]", "allowance": 1})
>db.users.insert({"username" : "inspired","email": "[email protected]", "allowance": 2})
>db.users.insert({"username" : "inspired","email": "[email protected]", "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.

Upvotes: 2

cenouro
cenouro

Reputation: 735

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.

Upvotes: 1

Related Questions