SQL NOT NULL

This tutorial shows you how to use the SQL NOT NULL constraint to enforce a column from storing NULL values.

Introduction to SQL NOT NULL constraint

The NOT NULL constraint is a column constraint that defines the rule which constrains a column to have non-NULL values only.

It means that when we use the INSERT statement to insert a new row into the table, we have to specify  the values for the NOT NULL columns.

The following statement illustrates the NOT NULL constraint syntax. It enforces the column_name to not accept any NULL values.

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

Logically, an NOT NULL constraint is equivalent to a CHECK constraint, therefore, the above statement is equivalent to the following statement.

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

For example, the following statement creates the training table that has the taken_date column with the NOT NULL constraint.

CREATE TABLE training (
    employee_id INT,
    course_id INT,
    taken_date DATE NOT NULL,
    PRIMARY KEY (employee_id , course_id)
);

Most relational database management systems add the NOT NULL constraint automatically by default to the primary key columns, therefore, we don’t have to specify it explicitly.

The following INSERT statement violates the NOT NULL constraint.

INSERT INTO training(employee_id,course_id)
VALUES(1,1);

ALTER TABLE NOT NULL statement

Typically, we define the NOT NULL constraints for columns when we create the table. However, sometimes, we want to change the constraint of a column that accepts a NULL value to not accept a NULL value.

To carry the change, we use these two steps:

First, update all current NULL values to non-NULL values using the UPDATE statement.

UPDATE table_name
SET column_name = 0
WHERE
    column_name IS NULL;

Note that we use the IS NULL operator in the WHERE clause to find the rows whose the  column_name is NULL.

Second, add the NOT NULL constraint to the column using the ALTER TABLE statement

ALTER TABLE table_name
MODIFY column_name data_type NOT NULL;

Suppose the taken_date column of the training table is NULL and we want to change it to NOT NULL.

First, we update all NULL values in the taken_date column to a specific date e.g., the current date.

UPDATE training
SET taken_date = CURRENT_DATE ()
WHERE
    taken_date IS NULL;

Second, we change the take_date column to NOT NULL constraint.

ALTER TABLE training 
MODIFY taken_date date NOT NULL;