If you’re getting error 1138 that reads “invalid use of NULL value“, it could be because you’re trying to change the definition of a column to require non-NULL values, but the column contains NULL values.
This can happen when we try to change a column from NOT NULL to NULL. It can also happen when we try to set a column as a primary key, but that column contains NULL values.
To fix this issue, be sure to ensure that the column you’re modifying doesn’t contain any NULL values. Alternatively, if the column should be able to accept NULL values, then you should change your column definition to allow NULL values.
Example of Error
Here’s an example of code that produces the error:
ALTER TABLE Products
MODIFY ProductDescription varchar(1000) NOT NULL;Result:
ERROR 1138 (22004): Invalid use of NULL value
Here, I’m trying to change the definition of the ProductDescription column to one that does not allow NULL values. I’m using NOT NULL to do this.
However, we get an error because the column already contains data with a NULL value.
Let’s look at the data:
SELECT * FROM Products;Result:
+---------------+---------------------+ | ProductName | ProductDescription | +---------------+---------------------+ | Hammer | Left handed edition | | Monkey Wrench | NULL | +---------------+---------------------+
As suspected, the column contains a NULL value (in the second row).
As mentioned, this error can also occur when we try to set a column to be a primary key, but the column contains one or more NULL values.
Solution 1
One option is to update the column so that it only contains a non-NULL values.
We can start by using a SELECT statement with the COUNT() function to see how many rows contain NULL values:
SELECT COUNT(*) FROM Products
WHERE ProductDescription IS NULL;Result:
1
We can also return the actual rows instead of just the count:
SELECT * FROM Products
WHERE ProductDescription IS NULL;Result:
+---------------+--------------------+ | ProductName | ProductDescription | +---------------+--------------------+ | Monkey Wrench | NULL | +---------------+--------------------+
We can update the NULL values by changing our SELECT statement into an UPDATE statement:
UPDATE Products
SET ProductDescription = 'N/A'
WHERE ProductDescription IS NULL;Result:
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Let’s check for NULL values again:
SELECT * FROM Products
WHERE ProductDescription IS NULL;Result:
Empty set (0.00 sec)
The column no longer contains any NULL values.
Let’s check the data again:
SELECT * FROM Products;Result:
+---------------+---------------------+ | ProductName | ProductDescription | +---------------+---------------------+ | Hammer | Left handed edition | | Monkey Wrench | N/A | +---------------+---------------------+
As expected, the NULL value has now been updated to N/A (which is a non-NULL value).
We can now go ahead and run the original code that produced the initial error:
ALTER TABLE Products
MODIFY ProductDescription varchar(1000) NOT NULL;Result:
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
This time we were able to run the code without error. We can verify our column definition by running a statement like the SHOW CREATE TABLE statement:
SHOW CREATE TABLE Products;Result:
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Products | CREATE TABLE `Products` ( `ProductName` varchar(255) NOT NULL, `ProductDescription` varchar(1000) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
This confirms that the ProductDescription column is now a NOT NULL column (i.e. it no longer accepts NULL values).
One thing to be aware of is that, if you’re getting the error while trying to set the column to be a primary key, then you’ll need to ensure that each row contains a unique value in the offending column. It’s no good setting a column to be a primary key if that column is going to contain the same value (such as N/A) across multiple rows.
Solution 2
If we determine that the column really shouldn’t have a NOT NULL constraint, we can simply change the definition to one that allows NULL values.
Example:
ALTER TABLE Products
MODIFY ProductDescription varchar(1000) NULL;Result:
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
This message indicates that we successfully modified the column to a definition that accepts NULL values. We used the NULL keyword to do this, but we could alternatively omitted that keyword (and NOT NULL) altogether, and the result would have been the same. Read on for an example of this.
Let’s Do it Again, Just to be Sure
When I did Solution 1, I removed the NULL value. This meant that I couldn’t really test Solution 2 as rigorously as I’d have liked to (because the column no longer contained a NULL value).
So, for the sake of completeness, let’s do the whole thing again from the start. We’ll put another NULL value into the column and then try to set the column to NOT NULL again.
Insert NULL into the column:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES ( 'Saw', NULL );Result:
Query OK, 1 row affected (0.01 sec)
Successfully inserted.
Check the data:
SELECT * FROM Products;Result:
+---------------+---------------------+ | ProductName | ProductDescription | +---------------+---------------------+ | Hammer | Left handed edition | | Monkey Wrench | N/A | | Saw | NULL | +---------------+---------------------+
OK, so our new row contains NULL.
Now let’s run the original code that produced original error:
ALTER TABLE Products
MODIFY ProductDescription varchar(1000) NOT NULL;Result:
ERROR 1138 (22004): Invalid use of NULL value
As expected, we get the error again.
Now let’s change that code to allow NULL values:
ALTER TABLE Products
MODIFY ProductDescription varchar(1000);Result:
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
This time the code ran without error.
This time I simply omitted NOT NULL from the definition. I didn’t use NULL in it’s place, I simply omitted it altogether. Either way, we get the same result – the column allows NULL values.
We can verify that this worked by looking at the table definition:
SHOW CREATE TABLE Products;Result:
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Products | CREATE TABLE `Products` ( `ProductName` varchar(255) NOT NULL, `ProductDescription` varchar(1000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
We can see that the column now allows NULL values and it defaults to NULL.
In MySQL, if a column allows NULL values, it’s defined with an explicit DEFAULT NULL clause. This means that if we don’t provide a value for that column, it will be set to NULL.