Unique Constraint with Two Fields in MongoDB

Question

I have a collection with fields "email" and "friends_email". I would like to setup a unique-ness constraint like the following, using MongoDB:

1. No record will have the same value for email and friends_email. So this would be invalid:

   {"email": "[e-mail]", "friends_email": "[e-mail]" }
   

2. No 2 records will have the same values for all fields. So the following examples would ALL be invalid:

   {
       { "email": "[e-mail]", "friends_email": "[e-mail]" },
       { "email": "[e-mail]", "friends_email": "[e-mail]" }
   }
   {
       { "email": "[e-mail]", "friends_email": null },
       { "email": "[e-mail]", "friends_email": null }
   }
   {
       { "email": null, "friends_email": "[e-mail]" },
       { "email": null, "friends_email": "[e-mail]" }
   }
   

   In plain english, it would be something like, the concatenation of email and friends_email will be unique, with null and undefined being coalesced into empty-string.

What's the best way to enforce this rule in MongoDB?

Answer 1

It sounds like you need a compound unique index:

db.users.createIndex( { "email": 1, "friends_email": 1 }, { unique: true } )

... and you can verify at the ORM layer that email =/= friends_email.

Answer 2

You can have compound unique index on email and friends_email field for ensuring the second case. But for the first case you need to handle that in the application code or use a java mapper such as Morphia to have a field-based validation. You might wanna check the following post also:

How to apply constraints in MongoDB?

Answer 3

for the second case, is a unique compound index what you're looking for?

 db.emails.ensureIndex( {email:1, friends_email:1}, { unique: true } )

As for the first case, I am not sure if there is a way to enforce the first rule. You may need to perform the check on the application side.