MySQL NOT NULL Constraint

Summary: in this tutorial, you will learn about MySQL NOT NULL constraints including defining a NOT NULL constraint for a column, adding a NOT NULL constraint to an existing column, and removing a NOT NULL constraint from a column.

Introduction to MySQL NOT NULL constraints

A NOT NULL constraint ensures that values stored in a column are not NULL. The syntax for defining a NOT NULL constraint is as follows:

column_name data_type NOT NULL;Code language: SQL (Structured Query Language) (sql)

A column may have only one NOT NULL constraint, which enforces the rule that the column must not contain any NULL values.

In other words, if you attempt to update or insert a NULL value into a NOT NULL column, MySQL will issue an error.

For example, the following creates the tasks table using the CREATE TABLE statement:

CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE
);Code language: SQL (Structured Query Language) (sql)

In the tasks table, we explicitly define the title and start_date columns with NOT NULL constraints.

The id column has the PRIMARY KEY constraint, therefore, it implicitly includes a NOT NULL constraint.

The end_date column can have NULL values, as when creating a new task, you may not know its completion date

The following shows the structure of the tasks table:

DESC tasks;

Output:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| title      | varchar(255) | NO   |     | NULL    |                |
| start_date | date         | NO   |     | NULL    |                |
| end_date   | date         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)Code language: PHP (php)

It’s a good practice to have the NOT NULL constraint in every column of a table unless you have a specific reason not to.

Generally, NULL values may complicate your queries because you need to use NULL-related functions such as ISNULL(), IFNULL(), and NULLIF() to handle them.

Adding a NOT NULL constraint to an existing column

Typically, you add NOT NULL constraints to columns when you create the table. However, you may want to add a NOT NULL constraint to a column of an existing table. In this case, you use the following steps:

First, check the current values of the column if there are any NULL values.
Second, update the NULL to non-NULL.
Third, modify the column with a NOT NULL constraint.

Consider the following example.

First, insert some rows into the tasks table:

INSERT INTO tasks(title ,start_date, end_date)
VALUES('Learn MySQL NOT NULL constraint', '2017-02-01','2017-02-02'),
      ('Check and update NOT NULL constraint to your database', '2017-02-01',NULL);Code language: SQL (Structured Query Language) (sql)

If you want to require users to provide an estimated end date when creating a new task, you can add a NOT NULL constraint to the end_date column of the tasks table.

Second, find rows with NULLs in the column end_date using the IS NULL operator:

SELECT * FROM tasks 
WHERE end_date IS NULL;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------------------------------------------------------+------------+----------+
| id | title                                                 | start_date | end_date |
+----+-------------------------------------------------------+------------+----------+
|  2 | Check and update NOT NULL constraint to your database | 2017-02-01 | NULL     |
+----+-------------------------------------------------------+------------+----------+
1 row in set (0.00 sec)Code language: PHP (php)

The query returned one row with NULL in the column end_date.

Third, update the NULL values to non-null values. In this case, you can create a rule that sets to one week after the start date when the end_date is NULL.

UPDATE tasks 
SET 
    end_date = start_date + 7
WHERE
    end_date IS NULL;Code language: SQL (Structured Query Language) (sql)

This query verifies the update:

SELECT * FROM tasks;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------------------------------------------------------+------------+------------+
| id | title                                                 | start_date | end_date   |
+----+-------------------------------------------------------+------------+------------+
|  1 | Learn MySQL NOT NULL constraint                       | 2017-02-01 | 2017-02-02 |
|  2 | Check and update NOT NULL constraint to your database | 2017-02-01 | 2017-02-08 |
+----+-------------------------------------------------------+------------+------------+
2 rows in set (0.00 sec)Code language: PHP (php)

Third, add a NOT NULL constraint to the end_date column using the following ALTER TABLE statement:

ALTER TABLE table_name
CHANGE 
   old_column_name 
   new_column_name column_definition;Code language: SQL (Structured Query Language) (sql)

In this case, the name of the old and new column names are the same except that the column must have a NOT NULL constraint:

ALTER TABLE tasks 
CHANGE 
    end_date 
    end_date DATE NOT NULL;Code language: SQL (Structured Query Language) (sql)

Finally, verify the change using the DESCRIBE statement:

DESC tasks;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| title      | varchar(255) | NO   |     | NULL    |                |
| start_date | date         | NO   |     | NULL    |                |
| end_date   | date         | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)Code language: PHP (php)

The output indicates that the NOT NULL constraint was added to the end_date column successfully.

Removing a NOT NULL constraint

To drop a NOT NULL constraint for a column, you use the ALTER TABLE..MODIFY statement:

ALTER TABLE table_name
MODIFY column_name column_definition;Code language: SQL (Structured Query Language) (sql)

Note that the column definition (column_definition) must restate the original column definition without the NOT NULL constraint.

For example, the following statement removes the NOT NULL constraint from the end_date column in the tasks table:

ALTER TABLE tasks 
MODIFY end_date DATE;Code language: SQL (Structured Query Language) (sql)

Here’s the structure of the tasks table:

DESC tasks;

Output:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| title      | varchar(255) | NO   |     | NULL    |                |
| start_date | date         | NO   |     | NULL    |                |
| end_date   | date         | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)Code language: PHP (php)

Summary

Use NOT NULL constraint to ensure that a column does not contain any NULL values.
Use ALTER TABLE ... CHANGE statement to add a NOT NULL constraint to an existing column.
Use ALTER TABLE ... MODIFY to drop a NOT NULL constraint from a column.

Was this tutorial helpful?