What Is a NOT NULL Constraint in SQL?

SQL has several ways of dealing with NULLs. In this article, we’ll focus on the NOT NULL constraint and other NULL-related clauses. Understand this and you’ll take an important step in your SQL growth!

Before we get straight into the NOT NULL constraint, let’s quickly define what NULLs and constraints are.

NULL values are a central concept in SQL databases. The idea behind a NULL value is simple: a NULL value means that we do not have a known value for that field.

SQL databases use constraints as rules that define what values can be stored in a column. There are different types of constraints: primary keys, check constraints, unique constraints, and NOT NULL constraints. Such tools ensure the accuracy and reliability of the data in the database.

In this article, we will cover the NOT NULL constraint, which is used to avoid having NULL values in a column.

Remember, a NULL value in a column represents the absence of a defined value. If for some reason we omit the value at the time of record creation, the field by default is set to NULL, meaning that NULL is the default value for the column.

If this seems a little too advanced for your current level of SQL, I’d like to suggest our SQL Constraints course. It will give you hands-on experience in creating and altering constraints in a relational database.

Why Use NULL Values?

What situations call for a NULL value? Perhaps we don’t know the value when we create a record. For example, suppose we have the table person with the column birth_date. We need to create a new record, but we don’t know the person’s birth_date yet. So, we can put a NULL value into the column birth_date and enter the actual value when we get it. The NULL is a placeholder for a missing value.

Another possibility is when the value for that column doesn’t exist. Continuing our person table example, suppose there is a column that stores the person’s driver_license_number. However, not everyone has a driver’s license, so this column should be nullable. Here, we use the NULL value to represent the value’s non-existence.

In other scenarios, we must avoid NULL values. There can be different reasons for that; sometimes the business meaning of the column doesn’t allow a missing value or sometimes we have a primary key on a column, which means this column will not accept NULL values. (All the values in a primary key column have to be unique and non-NULL.) Below we have some business scenarios where we need to use a NOT NULL constraint:

How to Define a NOT NULL Constraint in a Table

So, we’ve established that some columns should not ever contain a NULL, either because they’re part of a primary key or because of business reasons. Primary key columns are non-nullable by default, but in other cases we can use the NOT NULL constraint to avoid having any NULL values. NOT NULL constraints are defined at the table column level, as we can see in the following example:

CREATE TABLE person (
	social_security_number	INTEGER NOT NULL,
	last_name			VARCHAR(50) NOT NULL,
	first_name			VARCHAR(50),
	birth_date			DATE,
	driver_license_number	VARCHAR(12)
	)

In the above table creation statement, we can see two NOT NULL constraints on the columns social_security_number and last_name. If we omit the NOT NULL after the column definition (like we did for the first_name column) then the column will allow NULL values; moreover, the default value for this column will be NULL.

How a NOT NULL Constraint Works

In this section, we will demonstrate some SQL code used to create records with NULL values. For example, with the following INSERT we’ll create a record in the person table with NULL values in the columns birth_date and driver_license_number:

INSERT INTO person ( social_security_number, last_name, 
                     first_name, birth_date, driver_license_number )
            VALUES ( 1234567, ‘Smith’, ‘John’, NULL, NULL );

Next, we’ll show how to create the test table and how to insert records for two tests. As these tests will be held on a future date, the test result is not yet known. Thus, we’ll set the column test_result to NULL; this will represent the missing value for the test result.

CREATE TABLE test (
                     test_id      INTEGER,
                     planned_date DATE,
                     Test_result  BOOLEAN
                  );

INSERT INTO test   ( test_id, planned_date,  test_result )
            VALUES ( 10021,   ‘2021-06-15’,  NULL);

INSERT INTO test   ( test_id, planned_date)
            VALUES ( 10022,   ‘2021-07-15’);

Did you notice that we used two different ways to insert a record with a NULL value? The first INSERT uses an explicit NULL. The second INSERT omits the test_result column and its value; by default, a NULL is assigned to the test_result column field for that row.

What if we have a contact table but we don’t have all the expected information when we create a record for a new contact? In the example below, we show the creation of the table contact and how we can insert a record for a contact who didn’t provide an email address:

CREATE TABLE contact (
                     contact_last_name       VARCHAR(50),
                     contact_first_name      VARCHAR(50),
                     contact_phone           VARCHAR(50),
                     contact_email           VARCHAR(50),
                     );

INSERT INTO contact  ( contact_last_name, contact_last_name, 
                       contact_phone,     contact_email     )
            VALUES   ( ‘Doe’, ‘John’, ‘341-987-7652’,  NULL);

Inserting a NULL Into a NOT NULL Column

When are NOT NULL constraints checked? Every time an INSERT or UPDATE is executed against an SQL table, all the NOT NULL constraints in the affected table are checked.

As an example, let’s see what happens when we try to INSERT a record with a NULL value into a column with a NOT NULL constraint:

As an example, let’s see what happens when we try to INSERT a record with a NULL value into a column with a NOT NULL constraint:

INSERT INTO person ( social_security_number, last_name, 
                     first_name, birth_date, driver_license_number
                   )
            VALUES ( 1234567, NULL, ‘John’, ‘1994-03-03’,’23232323’);

We tried to put a NULL value in the column last_name, which is non-nullable. The database returned this error:

ERROR: null value in column “last_name” violates not-null constraint

We can obtain a similar error if we try to modify the value in a column with a NOT NULL constraint to NULL:

UPDATE person 
SET    last_name = NULL
WHERE  last_name = ‘Smith’;

And the error returned by the database is the same:

ERROR: null value in column “last_name” violates not-null constraint

For readers who want to go deeper with SQL constraints, I suggest the article What is a SQL constraint. You’ll learn the purposes and benefits of the most common SQL constraints.

Using Logical Conditions with NULL Values

In this section, we’ll describe some common situations related to NULL values and logical conditions.

One of the most common errors when working with NULL values is trying to identify NULL values using an equality condition:

WHERE driver_license_number = NULL 

The previous condition is wrong because we can’t use the ‘=’ operator against the NULL value – a very frequent mistake. The correct way to identify a NULL value is using the ‘IS NULL’ operator; the correct WHERE clause is:

WHERE driver_license_number IS NULL 

We can also use the opposite operator, IS NOT NULL, to test for the opposite condition:

WHERE driver_license_number IS NOT NULL 

Another important point is the value returned by the logical condition when one of the operand values involved is a NULL. Usually logical conditions return as TRUE or FALSE; however, when NULL is involved, a new possible value called UNKNOWN appears.

Suppose we want to get records for people who were born in this century. We can use a simple WHERE clause like WHERE birth_date >= ‘2000-01-01’. But what can we conclude about persons having a NULL in birth_date ?

In the above table, we can see the UNKNOWN value returned when the condition is “birth_date >= NULL”. This is reasonable – if we don’t know when this person was born, then UNKNOWN is a perfect result for this case.

For those readers who want to go a step further with NULL values, I suggest the article Understanding the Use of NULL in SQL Three-Valued Logic.

Want to Learn More About SQL Constraints?

In this article, we explained SQL’s NOT NULL constraints – how they work and how we can create a NOT NULL constraint. We also covered some points related to NULL values and logical conditions. If you’re interested in learning how to create and manage tables with various constraints, I recommend the Creating Database Structure course.

The articles  What Is a Primary Key in SQL and What Is a Foreign Key in SQL are also great explanations of some of SQL’s basic constraints. Check them out, continue learning, and increase your SQL skills!