0
447
ViewsWill unique indexes ignore fields that don't exist?
I have a MongoDB index:
Reservation.index(
{
source: 1,
accountID: 1, // <-- This is the only required field
confirmationCode_1: 1,
confirmationCode_2: 1,
confirmationCode_3: 1
},
{name: "Unique_reservation_index_1", unique: true}
);
Here are some sample entries I have in the database and I want to make sure that duplicates can't be made:
[
{
source: "A",
accountID: "AAA",
confirmationCode_1: "ABC"
},
{
source: "B",
accountID: "BBB",
confirmationCode_1: "ABC"
confirmationCode_2: "DEF"
},
{
source: "C",
accountID: "CCC",
confirmationCode_3: "GHI"
}
]
Sometimes I have confirmationCode_1 set and not confirmationCode_2 other times I both confirmationCode_1 and confirmationCode_2 set. Other times I have confirmationCode_3 set.
I want MongoDB to allow me to have the following doc (missing the confirmationCode_2 and confirmationCode_3 fields). Will it let me with the above index?
{
source: "A",
accountID: "123",
confirmationCode_1: "ABC"
}
Will it prevent me from adding two similar docs with confirmationCode_2 not defined or will that be considered the same? For example, if it does allow the above doc, will this be prevented?
{
source: "A",
accountID: "AAA",
confirmationCode_1: "ABC_2"
}
If I don't supply the confirmationCode_2 field, does it set the confirmationCode_2 field to null?
If I change the unique index to include sparse: true, how will it act differently?
Reservation.index(
{
source: 1,
accountID: 1, // <-- This is the only required field
confirmationCode_1: 1,
confirmationCode_2: 1
},
{name: "Unique_reservation_index_1", unique: true, sparse: true}
);
2 answers
Answer question0
From MongoDB document on unique Index,
A unique index ensures that the indexed fields do not store duplicate values
undefined / empty / null field is allowed as long as you do not have the same tuple of values of the fields in the compound index.
Below is my actual testing result:
You can observe that the document is successfully added under the unique index.
0
Will unique indexes ignore fields that don't exist?
No, the index will store a null value for this field, MongoDB will enforce uniqueness on the combination of the index key values.
//You have this docuemt on you MongoDB
{
source: "A",
accountID: "123",
confirmationCode_1: "ABC"
}
//You try to insert the next document, note the missing "accountID" field
//Even though "source" and "confirmationCode_1"
//This operation SUCCESS because
//MongoDB will enforce uniqueness on the "combination" of the index key values
{
source: "A",
confirmationCode_1: "ABC"
}
//You try to insert the next document
//The operation FAIL to insert the document
//because of the violation of the unique constraint
//on the combination of key values
{
source: "A",
accountID: "123",
confirmationCode_1: "ABC"
}
What if you change unique: true to unique: true, sparse: true ?
An index that is both sparse and unique prevents collection from having documents with duplicate values for a field but allows multiple documents that omit the key.