How can I specify uniqueness on multiple field in mongo, NOT combined?

Question

I have the following JSON schema in MongoDB:

{"email": "[email protected]", "second_email": "[email protected]"}

How can I enforce that both fields will be unique separately AND also be unique between them.

i.e the following document will not be valid:

{"email":"[email protected]", "second_email":"[email protected]"}

Because [email protected] is already exists in another document in the other field.

Answer 1

Off the top of my head, no database can do this (use another column/field as source data for uniqueness constraint). You will need to do some reshaping of data to achieve this. The easiest way is a unique constraint on an array field.

> db.foo.createIndex({ emails: 1 }, { unique: true } )

> db.foo.insert({ emails: ['[email protected]', '[email protected]'] })
WriteResult({ "nInserted" : 1 })
> db.foo.insert({ emails: ['[email protected]', '[email protected]'] })
WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 11000,
        "errmsg" : "E11000 duplicate key error index: test.foo.$emails_1 dup key: { : \"[email protected]\" }"
    }
})

Now, depending on your app logic, this emails array can even replace your original two fields. Or not. Up to you. If not, you'll need insert both the original fields and duplicate them in this array for the uniqueness check.