MySQL NOT NULL Constraint
Summary: in this tutorial, you will learn how to define a NOT NULL constraint for a column, add a NOT NULL constraint to an existing column, and remove a NOT NULL constraint from a column.
Introduction to the MySQL NOT NULL constraint
The NOT NULL constraint is a column constraint that ensures values stored in a column are not NULL.
The syntax of defining a NOT NULL constraint is as follows:
column_name data_type NOT NULL;
Code language: SQL (Structured Query Language) (sql)
A column may contain only one NOT NULL constraint which specifies a rule that the column must not contain any NULL value. In other words, if you update or insert NULL into a NOT NULL column, MySQL will issue an error.
The following CREATE TABLE statement creates the tasks table:
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, assuming that when you create a new task, you may not know when the task can be completed.
It’s a good practice to have the NOT NULL constraint in every column of a table unless you have a good reason not to do so.
Generally, the NULL value makes your queries more complicated because you have to use functions such as ISNULL(), IFNULL(), and NULLIF() for handling NULL.
Add a NOT NULL constraint to an existing column
Typically, you add NOT NULL constraints to columns when you create the table. Sometimes, you want to add a NOT NULL constraint to a NULL-able column of an existing table. In this case, you use the following steps:
- Check the current values of the column if there is any
NULL. - Update the
NULLto non-NULLifNULLsexist. - Modify the column with a
NOT NULLconstraint.
Consider the following example.
The following statement inserts some rows into the tasks table for the demonstration.
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)
Suppose that you want to force users to give an estimated end date when creating a new task. To implement this rule, you add a NOT NULL constraint to the end_date column of the tasks table.
First, use the IS NULL operator to find rows with NULLs in the column end_date :
SELECT *
FROM tasks
WHERE end_date IS NULL;
Code language: SQL (Structured Query Language) (sql)
The query returned one row with NULL in the column end_date.
Second, update the NULL values to non-null values. In this case, you can make up a rule that if the end_date is NULL, the end date is one week after the start date.
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)
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)
Let’s verify the change by using the DESCRIBE statement:
DESCRIBE tasks;
Code language: SQL (Structured Query Language) (sql)
As you see, the NOT NULL constraint was added to the end_date column successfully.
Drop 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 NULLconstraint.
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
end_date DATE NOT NULL;Code language: SQL (Structured Query Language) (sql)
To ensure that the statement actually removed the NOT NULL constraint, you can use the SHOW CREATE TABLE command to view the full column definition:
DESCRIBE statement also does the trick:DESCRIBE tasks;
In this tutorial, you have learned how to define a NOT NULL constraint for a column, add a NOT NULL constraint to a column, and remove a NOT NULL constraint from a column.