In SQL, constraints are essential for maintaining data integrity and ensuring the accuracy and reliability of the data stored within a database. One of the most commonly used constraints in SQL databases is the NOT NULL constraint.

In this article, I provide a quick overview of the NOT NULL constraint, its purpose, syntax, usage, and practical examples.

What is the NOT NULL Constraint?

When we create a column in SQL, we have the option of defining it as NOT NULL. You may have seen this included in SQL scripts that creates tables. This is called a NOT NULL constraint.

The NOT NULL constraint is used to ensure that a column cannot have a NULL value. When a column is defined as NOT NULL, it becomes mandatory to insert a value in that column for every row in the table. If an attempt is made to insert a NULL value into a NOT NULL column, the database will reject the operation and return an error.

This constraint is essential for fields where a missing or undefined value would be unacceptable or lead to data inconsistency. For example, a primary key column, which uniquely identifies each row in a table, must always have a value, and thus, it is typically defined with the NOT NULL constraint. In fact, many RDBMSs will prevent us from adding a primary key to a nullable column. A nullable column is one that hasn’t been defined as NOT NULL, or has been defined as NULL.

Syntax of NOT NULL in SQL

The NOT NULL constraint can be defined when creating or altering a table. Below is the basic syntax for adding the NOT NULL constraint during table creation:

CREATE TABLE table_name (
    column_name data_type NOT NULL,
    ...
);

Alternatively, the NOT NULL constraint can be added to an existing table using the ALTER TABLE statement:

ALTER TABLE table_name
MODIFY column_name data_type NOT NULL;

Defining a Nullable Column

Many RDBMSs allow us to explicitly define a column as nullable by using NULL without the NOT. In this case, we simply omit the NOT from the above examples.

Given columns are nullable by default, we can either specify NULL or simply omit NULL and NOT NULL altogether.

So both of the following are equivalent:

CREATE TABLE table_name (
    column_name data_type NULL,
    ...
);

CREATE TABLE table_name (
    column_name data_type,
    ...
);

And both of these are equivalent too:

ALTER TABLE table_name
MODIFY column_name data_type NULL;

ALTER TABLE table_name
MODIFY column_name data_type;

NULL isn’t strictly a constraint, but it does allow us to be explicit about making the column nullable.

Usage and Examples

Here are a couple of examples that illustrate how to create NOT NULL constraints. In the first case we create it while creating the table. In the second example we add it to an existing table.

Example 1: Creating a Table with a NOT NULL Constraint

Consider a scenario where we want to create a users table with columns for user_id, username, and email, where both user_id and username are mandatory fields:

CREATE TABLE users (
    user_id INT PRIMARY KEY NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

In this example, the user_id and username columns are defined with the NOT NULL constraint, ensuring that every user must have a unique identifier and a username. The email field, however, is optional and can be left empty.

Example 2: Altering a Table to Add a NOT NULL Constraint

If you have an existing table orders with a column order_date that currently allows NULL values, but you want to enforce that every order must have a date, you can modify the table as follows:

ALTER TABLE orders
MODIFY order_date DATE NOT NULL;

After this alteration, any attempt to insert or update a record in the orders table without a value in the order_date column will result in an error.

Importance of the NOT NULL Constraint

• Data Integrity: The primary reason to use NOT NULL is to ensure data integrity. It prevents the entry of NULL values where they are not appropriate, which can be crucial for maintaining accurate records.
• Avoiding Logical Errors: In many applications, NULL can lead to logical errors or unexpected behavior in queries and calculations. For instance, using NULL in mathematical operations or comparisons can yield results that are not meaningful.
• Improving Query Performance: Knowing that a column will never contain a NULL value can help the SQL engine optimize queries, potentially leading to better performance.
• Enforcing Business Rules: Often, business logic dictates that certain fields must always have a value (e.g., an employee_id in a payroll system). The NOT NULL constraint enforces these rules at the database level, ensuring consistency.

Best Practices for Using NOT NULL

• Identify Mandatory Fields: Before defining the schema, carefully analyze which fields must always have a value and apply the NOT NULL constraint accordingly.
• Combine with Other Constraints: Often, NOT NULL is used in conjunction with other constraints, such as UNIQUE or PRIMARY KEY, to enforce additional rules on the data.
• Avoid Overuse: While the NOT NULL constraint can be useful, it should be used judiciously. Not every column needs this constraint, and overusing it can reduce flexibility in data entry.