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 ofNULL
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, usingNULL
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). TheNOT 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 asUNIQUE
orPRIMARY 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.