MySQL NOT NULL Constraint
Summary: in this tutorial, you will learn how to define a NOT NULL
the 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
the constraint is a column constraint that ensures values stored in a column are not NULL
.
The syntax of defining a NOT NULL
the constraint is as follows:
column_name data_type NOT NULL;
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
);
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
NULL
to non-NULL
ifNULLs
exist. - Modify the column with a
NOT NULL
constraint.
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);
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;
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;
This query verifies the update:
SELECT * FROM tasks;
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;
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;
Let’s verify the change by using the DESCRIBE
statement:
DESCRIBE tasks;
As you see, the NOT NULL
the constraint was added to the end_date
column successfully.
Drop a NOT NULL
constraint
To drop a NOT NULL
the constraint for a column, you use the ALTER TABLE..MODIFY
statement:
ALTER TABLE table_name
MODIFY column_name column_definition;
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
end_date DATE NOT NULL;
To ensure that the statement actually removed the NOT NULL
constraint, you can use the SHOW CREATE TABLE
the command to view the full column definition:
DESCRIBE
the statement also does the trick:DESCRIBE tasks;
In this tutorial, you have learned how to define a NOT NULL
the constraint for a column, add a NOT NULL
constraint to a column, and remove a NOT NULL
constraint from a column.
0 Comments
CAN FEEDBACK
Emoji