Reputation: 31612
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
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
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