intro
Let's master MySQL nullable columns by understanding how MySQL behaves when creating a column and the differences between nullable and non-nullable columns.
If you have dealt with SQL, you know that not all columns in a database have a value. The reason is that a database represents data in the real world, and information is not always available. This is why MySQL nullable columns are so popular.
In this article, you will learn more about nullability in MySQL, understand how this mechanism works, and how to use it. Are columns nullable by default in MySQL? Time to find out!
What Is a MySQL Nullable Column?
In MySQL, a nullable column is a column in a database table that can store NULL values. In databases, NULL represents the absence of a value.
In short, the difference between a nullable and non-nullable column in MySQL is that the nullable column allows NULL values, while the non-nullable column does not permit NULL values.
How to Declare a Column as Nullable in MySQL
In MySQL, columns can store NULL values by default unless specified otherwise. So, if you're wondering, "Are columns nullable by default in MySQL?" the answer is a resounding "Yes!"
At the same time, you can explicitly declare a column as nullable for better readability by specifying the NULL keyword in the column definition when creating or modifying a table.
See how to do that below!
Define a Nullable Column When Creating a Table
You can explicitly declare a column as nullable during table creation with CREATE TABLE by marking it with NULL or DEFAULT NULL, as in the example below:
1
CREATE TABLE users (
2
id INT PRIMARY KEY AUTO_INCREMENT,
3
full_name VARCHAR(100) NOT NULL,
4
email VARCHAR(100) NOT NULL,
5
birth_date DATE NULL -- nullable column
6
);
Here, the email column is explicitly declared as nullable, while the full_name and email columns are non-nullable—as they are marked with NOT NULL.
Add a Nullable Column to an Existing Table
You can explicitly add a nullable column to a table with ALTER TABLE by marking it with NULL:
1
ALTER TABLE users ADD addressed VARCHAR(255) NULL;
This will add a MySQL nullable column named addressed to the users table. Learn more in our guide on the ALTER TABLE ADD COLUMN statement in SQL.
Set a Column to Nullable
If you need to change an existing column to be nullable, you can use the ALTER TABLE statement as follows:
1
ALTER TABLE users MODIFY email VARCHAR(100) NULL;
This command modifies the email column in the users table to allow NULL values. Again, the key lies in the NULL keyword.
Note: To update a column to nullable, the NULL keyword must be specified explicitly. Otherwise, MySQL leaves the column as is.
Why Are Columns Nullable By Default in MySQL?
Note that explicitly marking a column as nullable when creating it or adding it to a table is not required, as MySQL columns are nullable by default.
That means if you do not specify NULL or NOT NULL in a column definition or update statement, MySQL will assume you intended the column to be nullable (NULL) or not nullable (NOT NULL). Thus, MySQL automatically marks columns with NULL unless instructed otherwise.
That default behavior makes sense as MySQL aims to provide flexibility in data entry. Allowing columns to accept NULL values ensures that incomplete or optional data can still be inserted without errors.
Marking columns as non-nullable would add a tedious—and most likely, undesired— data integrity condition, which would result in a lot of blocked record insertions or updates.
How to Check If a Column Is Nullable in MySQL
Now, suppose you want to find out whether the birth_date column in your users table is nullable or not. There are three approaches to reach this goal.
Let’s explore them all!
Approach #1: Use an IS NULL Query
The first idea is to perform a query to see if the desired column contains at least one NULL value. If it does, that is enough to say that the column is nullable.
In our example scenario, you can check that with:
1
SELECT birth_date
2
FROM users
3
WHERE birth_date IS NULL;
The problem with this approach is that it only works if the column currently contains NULL values. If no NULL values exist in the column, you will not be able to confirm whether it is nullable or not!
Approach #2: Inspect the DDL of a Table
The second approach is to use the SHOW CREATE TABLE statement to get the SQL DDL (Data Definition Language) creation query of the users table:
1
SHOW CREATE TABLE users;
The result will look something like this:
1
CREATE TABLE `users` (
2
`id` int **NOT NULL** AUTO_INCREMENT,
3
`full_name` varchar(100) **NOT NULL**,
4
`email` varchar(100) **DEFAULT NULL**,
5
`birth_date` date **DEFAULT NULL**,
6
PRIMARY KEY (`id`)
7
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
The resulting DDL table creation query helps you understand that birth_date is actually nullable.
Approach #3: Use a Complete Database Tool Like DbVisualizer
DbVisualizer is a top-rated database client that comes with many advanced features. Among them is the ability to see the DDL creation query of a table with a single click.
All you have to do is download the Pro version of DbVisualizer, activate the 21-day trial, connect to your database, reach the table you want to explore, and open it in a new tab:
Then, click on the "DDL" tab on the right side of the interface:
The DDL of the table will be displayed, just like in Approach #2, but with just a few clicks!
Again, you will be able to tell that the birth_date column is a MySQL nullable column.
Conclusion
In this blog post, you learned what a MySQL nullable column is and how this mechanism works. As demonstrated here, to determine whether a column in MySQL is nullable, there are several approaches, and using DbVisualizer is the easiest one.
In addition to connecting to dozens of DBMSs, DbVisualizer offers advanced query optimization capabilities, visual SQL DDL exploration, and full support for all MySQL features. Download DbVisualizer for free now!
FAQ
Are columns nullable by default in MySQL?
Yes, columns are nullable by default in MySQL unless explicitly marked as NOT NULL. If neither NULL nor NOT NULL is specified in the column definition, MySQL assumes the column is nullable. While nullable columns are the default, it is a good practice to explicitly define DEFAULT NULL|NOT NULL for clarity and maintainability of your schema definition queries.
How do I modify a MySQL column to allow NULL values?
To modify a MySQL column to allow NULL values, you can use the ALTER TABLE statement with the MODIFY keyword, followed by the column name, data type, and NULL keyword:
1
ALTER TABLE table_name MODIFY column_name datatype NULL;
Can a non-nullable column in MySQL become nullable?
Yes, a non-nullable column in MySQL can be changed to nullable using the ALTER TABLE statement. You can modify the column by specifying NULL in the MODIFY clause.
Can a MySQL nullable column become non-nullable?
Yes, a MySQL nullable column can be changed to non-nullable through the ALTER TABLE MODIFY statement. However, before making this change, you must ensure that there are no existing NULL values in the column. Otherwise, the query will terminate with the following error:
1
Data truncation: Invalid use of NULL value
When to use NULL in MySQL tables?
Use NULL in MySQL tables when data is optional or unknown. It helps differentiate between "no data" (which may be symbolized by an empty value) and other values commonly associated with NULL (zero strings and the like.)
Additionally, using NULL can enhance data integrity by allowing you to clearly represent missing information.
