MongoDB: Is there something like ‘NOT NULL’ constraint in SQL?

Overview

MongoDB, as a NoSQL database, offers a flexible schema design which permits a wide variety of data types and structures within a single collection. This flexibility often leads to questions about how to enforce data integrity, similar to the constraints found in SQL databases. A common question that arises is whether MongoDB offers a ‘NOT NULL’ constraint that is analogous to what you find in SQL database systems and how to implement such a functionality.

Broadly speaking, MongoDB does not include a ‘NOT NULL’ constraint in the same way SQL databases do. However, data integrity can still be enforced using schema validation, which was introduced in version 3.2. In this tutorial, we will discuss different strategies to simulate the ‘NOT NULL’ constraint in MongoDB by walking through basic to advanced code examples.

Understanding MongoDB Schema Validation

In SQL, you would typically enforce a ‘NOT NULL’ constraint when defining your table columns:

CREATE TABLE Users (
  ID INT PRIMARY KEY,
  UserName VARCHAR(50) NOT NULL,
  Email VARCHAR(100) NOT NULL
);

In MongoDB, you could use schema validation to enforce similar rules by utilizing the $jsonSchema keyword:

db.createCollection("users", {
  validator: { 
    $jsonSchema: {
      bsonType: "object",
      required: [ "userName", "email" ],
      properties: {
        userName: {
          bsonType: "string",
          description: "must be a string and is required"
        },
        email: {
          bsonType: "string",
          description: "must be a string and is required"
        }
      }
    }
  }
});

This would create a ‘users’ collection where both userName and email fields are required.

Basic NOT NULL Simulation

One way to simulate a ‘NOT NULL’ constraint is by integrating the required document validation upon the creation of a collection or adding it to an existing collection:

db.runCommand({
  collMod: "users",
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: [ "userName", "email" ]
    }
  }
});

Once you apply this validation rule, any insert or update that does not include the userName and email fields will fail.

Intermediate Constraints

For a more sophisticated requirement that ensures not just the presence but also the non-emptiness of a field, you could use the $expr operator:

db.runCommand({
  collMod: "users",
  validator: {
    $expr: {
      $and: [
        { $ne: [ "$userName", "" ] },
        { $ne: [ "$email", "" ] }
      ]
    }
  }
});

This ensures that the userName and email fields cannot be empty strings.

Advanced Validation Techniques

To create a complex validation rule that could compare two fields to implement ‘NOT NULL’ logic on one based on the state of another, use custom logic with the $where clause:

db.users.find({$where: function() {
  return (this.userName != null && this.email != null);
}});

While the $where clause can grant significant power in querying, be cautious, as it may come with performance costs.

Handling NULL or Missing Values in Aggregations

In aggregation operations, you might encounter scenarios where you want to exclude documents that have ‘NULL’ or missing values in a specific field. The $match stage can filter out such documents:

db.users.aggregate([
  { $match : { email : { $ne : null } } }
]);

This aggregation will only include documents that have a non-null email field.

Enforcing Constraints on Embedded Documents

If your documents include embedded documents or arrays, you can enforce ‘NOT NULL’ constraints within them as well:

db.createCollection("products", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["name", "details"],
      properties: {
        name: {
          bsonType: "string",
          description: "must be a string and is required"
        },
        details: {
          bsonType: "object",
          required: ["manufacturer", "price"],
          properties: {
            manufacturer: {
              bsonType: "string",
              description: "must be a string and is required"
            },
            price: {
              bsonType: "decimal",
              description: "must be a decimal and is required"
            }
          }
        }
      }
    }
  }
});

Such a definition enforces that every product has a name and details, with the details embedded document containing manufacturer and price.

Practical Tips

When enforcing constraints in MongoDB:

• Remember to balance between strict schema validation and the flexible nature of MongoDB.
• Consider database performance implications when using custom validation expressions, especially for large collections or complex validations.
• Use indexing strategies to assist with the efficiency of queries involving these validation rules.

Conclusion

Even though MongoDB does not have the traditional ‘NOT NULL’ constraint found in SQL databases, MongoDB’s schema validation framework provides powerful tools to ensure that the database adheres to specific data integrity rules. By effectively utilizing the MongoDB validation features, one can simulate ‘NOT NULL’ constraints and safeguard data quality.