CODEWITHSUNDEEP
mongodb
Randy L
Randy L

Reputation: 14746

Unique Constraint with Two Fields in MongoDB

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": "[email protected]", "friends_email": "[email protected]" }

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

    {
    { "email": "[email protected]", "friends_email": "[email protected]" },
    { "email": "[email protected]", "friends_email": "[email protected]" }
}
{
    { "email": "[email protected]", "friends_email": null },
    { "email": "[email protected]", "friends_email": null }
}
{
    { "email": null, "friends_email": "[email protected]" },
    { "email": null, "friends_email": "[email protected]" }
}

    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?

Upvotes: 45

Views: 43020

Answers (4)

Katya Kamenieva
Katya Kamenieva

Reputation: 376

For #1 you can set up checks on the database side using Schema Validaiton:

validator: {
  "$expr": {
    "$ne": [ "$email", "$friends_email" ]
  }
}

db.runCommand( { collMod: "collectionName", validator: validator} )

Upvotes: 1

Winfield
Winfield

Reputation: 19145

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.

Upvotes: 65

Kay
Kay

Reputation: 3012

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.

Upvotes: 8

cubbuk
cubbuk

Reputation: 7920

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?

Upvotes: 10

Related Questions