MySQL NOT NULL

In MySQL, the NOT NULL is used to constrain a column cannot include a NULL value.

`NOT NULL` Syntax

Define NOT NULL columns

Please use the following syntax to define a NOT NULL column:

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

Add NOT NULL constraint

To add a NOT NULL constraint, modify the definition of the column using the following syntax:

ALTER TABLE table_name
MODIFY column_name data_type NOT NULL ...;

Here, add the NOT NULL keyword in the the definition of the columns.

Remove NOT NULL constraint

To drop a NOT NULL constraint, please modify the definition of the column using the following syntax:

ALTER TABLE table_name
MODIFY column_name data_type ...;

Here, just remove the NOT NULL keyword.

NOT NULL Examples

Let’s understand NOT NULL by a example. Please follow the steps below:

Create a user_hobby table:

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
  `hobby_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `hobby` VARCHAR(45) NOT NULL
);

Here, values are not accepted in the user_id and hobby columns NULL.

Insert some rows of into the table

INSERT INTO `user_hobby` (`user_id`, `hobby`)
VALUES (1, 'Football'), (1, 'Swimming');

Obviously it can be inserted successfully.

Insert a NULL value into hobby column :
```
INSERT INTO `user_hobby` (`user_id`, `hobby`)
VALUES (1, NULL);
```
The MySQL server returns the following error: ERROR 1048 (23000): Column 'hobby' cannot be null.

Modify an existing column to be non-nullable

If you want to modify an existing column that allows NULL values to not allow NULL values, please modify the NULL value to non-nullable values, otherwise you may encounter errors.

Suppose, we have the following table:

DROP TABLE IF EXISTS user_hobby;
CREATE TABLE `user_hobby` (
  `hobby_id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `hobby` VARCHAR(45)
);

Here, hobby columns can have NULL values.

Now we insert some rows for testing:

INSERT INTO `user_hobby` (`user_id`, `hobby`)
VALUES (1, 'Football'), (1, NULL);

Now let’s look at all rows in the table:

SELECT * FROM `user_hobby`;

+----------+---------+----------+
| hobby_id | user_id | hobby    |
+----------+---------+----------+
|        1 |       1 | Football |
|        2 |       1 | NULL     |
+----------+---------+----------+
2 rows in set (0.00 sec)

If we run the following statement and want to modify the hobby column to be NOT NULL:

ALTER TABLE `user_hobby`
MODIFY `hobby` VARCHAR(45) NOT NULL;

MySQL will return the following error: ERROR 1138 (22004): Invalid use of NULL value. This is because the NULL value in the hobby column prevents this operation.

We should first change the NULL values in the hobby column to non-NULL values:

UPDATE `user_hobby`
SET `hobby` = 'NOTHING'
WHERE `hobby` IS NULL;

Then we are modifying the definition of the hobby column:

ALTER TABLE `user_hobby`
MODIFY `hobby` VARCHAR(45) NOT NULL;

The NOT NULL constraint has now been successfully added to the hobby column.

Conclusion

In this article, we learned how to define a NOT NULL column in MySQL.

NOT NULL Syntax