Set MongoDB compound index with a fixed value field

A MongoDB compound index with a fixed value field ensures uniqueness across multiple fields, including fields that may be missing (null) in some documents. When you create a unique compound index, MongoDB treats missing fields as null values.

Syntax

db.collection.createIndex(
    { "field1": 1, "field2": 1 }, 
    { unique: true }
);

Create Sample Data

First, let's create a unique compound index on StudentName and StudentAge ?

db.compoundIndexDemo.createIndex(
    {"StudentName": 1, "StudentAge": 1}, 
    {unique: true}
);

{
    "createdCollectionAutomatically" : true,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}

Example: Testing Unique Constraint

Insert documents to see how the compound index handles missing and duplicate values ?

db.compoundIndexDemo.insertMany([
    {"StudentName": "Chris"},
    {"StudentName": "Chris", "StudentAge": 23},
    {"StudentName": "Chris", "StudentAge": 22}
]);

{
    "acknowledged" : true,
    "insertedIds" : [
        ObjectId("5e084c1d25ddae1f53b62207"),
        ObjectId("5e084c5625ddae1f53b62209"),
        ObjectId("5e084c5b25ddae1f53b6220a")
    ]
}

Now try to insert a duplicate combination ?

db.compoundIndexDemo.insertOne({"StudentName": "Chris", "StudentAge": 23});

WriteError: E11000 duplicate key error collection: web.compoundIndexDemo 
index: StudentName_1_StudentAge_1 dup key: { : "Chris", : 23.0 }

Verify Result

db.compoundIndexDemo.find();

{ "_id" : ObjectId("5e084c1d25ddae1f53b62207"), "StudentName" : "Chris" }
{
    "_id" : ObjectId("5e084c5625ddae1f53b62209"),
    "StudentName" : "Chris",
    "StudentAge" : 23
}
{
    "_id" : ObjectId("5e084c5b25ddae1f53b6220a"),
    "StudentName" : "Chris",
    "StudentAge" : 22
}

Key Points

• Missing fields are treated as null in compound indexes
• The combination ("Chris", null) is different from ("Chris", 23)
• You cannot insert another document with just {"StudentName": "Chris"} due to the unique constraint

Conclusion

MongoDB compound indexes with unique constraints enforce uniqueness across all specified fields, treating missing fields as null values. This allows documents with different field combinations to coexist while preventing true duplicates.