If you’re getting MySQL error 1364, that reads something like Field 'column_name' doesn't have a default value, where column_name is the name of a column in your table, it’s probably because you’re trying to perform an operation that retrieves the column’s default value, but it doesn’t have a default value.
This can happen when the column is set to NOT NULL and hasn’t been defined with an explicit default value.
The solution to this issue will depend on what we’re trying to do. Below are examples of how to fix this error.
Example of Error
Here are some scenarios that will produce the error.
Suppose we create the following table:
CREATE TABLE Products (
ProductId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
ProductName varchar(255) NOT NULL,
ProductDescription varchar(1000),
Price decimal(8,2)
);And we now try to get the default value for the ProductName column:
SELECT
DEFAULT(ProductName)
FROM Products;Result:
ERROR 1364 (HY000): Field 'ProductName' doesn't have a default value
It returned error 1364. The reason for this is because the ProductName column doesn’t have a default value set and it’s specified as NOT NULL.
Here’s another scenario that will produce the same error:
INSERT INTO Products ( ProductId )
VALUES ( 1 );Result:
ERROR 1364 (HY000): Field 'ProductName' doesn't have a default value
In this case, I tried to insert a row into the table, but I didn’t specify a value for the ProductName column. MySQL returned an error because the column has no default value and it’s a NOT NULL column.
Solution 1 – Provide a Value
If we get the error when trying to insert data, one solution is to ensure we provide a value for the column.
So for example, we could change the above INSERT statement to the following:
INSERT INTO Products ( ProductId, ProductName )
VALUES ( 1, 'Hammer' );Result:
Query OK, 1 row affected (0.00 sec)
This time the row was inserted.
Note that I didn’t provide any values for the ProductDescription and Price columns, but they didn’t cause an error.
The reason those two columns didn’t produce an error is because they are nullable columns (i.e. they aren’t defined as NOT NULL). When we define a column without a NOT NULL clause, MySQL implicitly adds an explicit DEFAULT clause to the column.
We can verify this by using SHOW CREATE TABLE to check the table’s definition:
SHOW CREATE TABLE Products;Result:
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Products | CREATE TABLE `Products` ( `ProductId` int NOT NULL AUTO_INCREMENT, `ProductName` varchar(255) NOT NULL, `ProductDescription` varchar(1000) DEFAULT NULL, `Price` decimal(8,2) DEFAULT NULL, PRIMARY KEY (`ProductId`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
We can see that the table’s definition has DEFAULT clauses against both the ProductDescription column and the Price column, even though we didn’t specify those DEFAULT clauses when we created the table. MySQL added the clauses due to the fact that they were nullable columns. But it didn’t add any DEFAULT clauses to our ProductName column, because we explicitly defined that column as NOT NULL. As mentioned, MySQL doesn’t implicitly add any DEFAULT clauses to NOT NULL columns. It’s up to us to specify such a clause.
Solution 2 – Use the IGNORE Clause
Another option is to use the IGNORE clause. The IGNORE clause will ignore the error and insert the row anyway. In this case it will insert the implicit default value for the data type, and downgrade the error to a warning.
Example:
INSERT IGNORE INTO Products ( ProductId )
VALUES ( 2 );Result:
Query OK, 1 row affected, 1 warning (0.00 sec)
This time we didn’t get an error. Instead, one row was inserted and we got a warning.
Let’s check the warning:
SHOW WARNINGS;Result:
+---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 1364 | Field 'ProductName' doesn't have a default value | +---------+------+--------------------------------------------------+
The warning tells us the same thing that the error message told us – that the field doesn’t have a default value. However, in this case the row was actually inserted.
Let’s take a look at the table:
SELECT * FROM Products;Result:
+-----------+-------------+--------------------+-------+ | ProductId | ProductName | ProductDescription | Price | +-----------+-------------+--------------------+-------+ | 1 | Hammer | NULL | NULL | | 2 | | NULL | NULL | +-----------+-------------+--------------------+-------+
We now have two rows in the table. Given we provided a value for just one column, MySQL inserted the implicit default values for the other three columns. Specifically, it inserted an empty string in the ProductName column, and NULL into the other two columns.
You may be wondering why the ProductName column has an empty string and the ProductDescription column has NULL, despite them both being string types?
You may recall that MySQL generated an explicit DEFAULT NULL clause for the ProductDescription column (and for the Price column) when we defined those columns without a NOT NULL constraint. That’s why MySQL inserted NULL into those columns now.
But for the ProductName column, we explicitly defined that column as NOT NULL, which meant that MySQL didn’t generate a DEFAULT clause for that column. And now that we are trying to insert data without a value, MySQL inserts the implicit default value for the data type (which is an empty string for string data types).
Solution 3 – Disable Strict Mode
We can alternatively disable strict SQL mode. By doing this, we’ll get the same result as the previous example without having to use the IGNORE keyword.
First, let’s check our current SQL mode:
SELECT @@SESSION.sql_mode;Result:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
We can see that STRICT_TRANS_TABLES is included in my list of SQL modes. This enables strict mode. It would be the same if our SQL mode contained STRICT_ALL_TABLES. Both of those enable strict mode.
To disable strict mode, we can either remove these specific settings from our SQL mode, or we can simply explicitly set our SQL mode to be either blank or to have the settings that we want to keep.
Example:
SET @@sql_mode = sys.list_drop(@@sql_mode, 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO');Result:
Query OK, 0 rows affected (0.00 sec)
In this case I removed four settings. The other three settings are related to strict mode, and so I decided to drop them from the list.
Here’s what our SQL mode looks like now:
SELECT @@SESSION.sql_mode;Result:
ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION
So I’ve disabled strict mode.
Now let’s try inserting another row like we did above (but this time without the IGNORE clause):
INSERT INTO Products ( ProductId )
VALUES ( 3 );Result:
Query OK, 1 row affected, 1 warning (0.01 sec)
We get the same outcome that we got when we used the IGNORE clause.
The warning is the same:
SHOW WARNINGS;Result:
+---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 1364 | Field 'ProductName' doesn't have a default value | +---------+------+--------------------------------------------------+
Let’s check the data:
SELECT * FROM Products;Result:
+-----------+-------------+--------------------+-------+ | ProductId | ProductName | ProductDescription | Price | +-----------+-------------+--------------------+-------+ | 1 | Hammer | NULL | NULL | | 2 | | NULL | NULL | | 3 | | NULL | NULL | +-----------+-------------+--------------------+-------+
We get the same outcome as far as the data insert goes. The ProductName contains an empty string, and the ProductDescription and Price columns contain NULL.
Solution 4 – Remove the NOT NULL Clause
If we find ourselves trying to implement solutions that override the NOT NULL clause, then perhaps the column really shouldn’t have a NOT NULL clause in the first place.
If we determine that the column shouldn’t be NOT NULL, then we should remove NOT NULL from its definition.
Example:
ALTER TABLE Products
MODIFY ProductName varchar(255) NULL;Result:
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
Here, I explicitly used NULL to define the column as nullable, but I could have omitted this if I wanted. Either way, the column would still have been defined as nullable.
Let’s now use SHOW CREATE TABLE to take a look at the table definition:
SHOW CREATE TABLE Products;Result:
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Products | CREATE TABLE `Products` ( `ProductId` int NOT NULL AUTO_INCREMENT, `ProductName` varchar(255) DEFAULT NULL, `ProductDescription` varchar(1000) DEFAULT NULL, `Price` decimal(8,2) DEFAULT NULL, PRIMARY KEY (`ProductId`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
We can see that the ProductName column is now defined as DEFAULT NULL.
Let’s insert another row:
INSERT INTO Products ( ProductId )
VALUES ( 4 );Result:
Query OK, 1 row affected (0.00 sec)
OK, success.
Now let’s look at the data:
SELECT * FROM Products;Result:
+-----------+-------------+--------------------+-------+ | ProductId | ProductName | ProductDescription | Price | +-----------+-------------+--------------------+-------+ | 1 | Hammer | NULL | NULL | | 2 | | NULL | NULL | | 3 | | NULL | NULL | | 4 | NULL | NULL | NULL | +-----------+-------------+--------------------+-------+
This time the ProductName column contains NULL instead of an empty string. That’s because the column was defined with an explicit DEFAULT NULL when we defined it as NULL.
Solution 5 – Add a DEFAULT Clause
If we simply add our own DEFAULT clause to our column/s, we would save ourselves the trouble of having to decide which of the above solutions we want to implement, and we wouldn’t have to make do with the default values that MySQL uses when it implicitly adds the DEFAULT clause.
Let’s redefine our table with our own DEFAULT clause:
ALTER TABLE Products
MODIFY ProductName varchar(255) NOT NULL DEFAULT 'N/A';Result:
Query OK, 4 rows affected, 1 warning (0.01 sec) Records: 4 Duplicates: 0 Warnings: 1
It worked, but we got a warning.
Let’s check the warning:
SHOW WARNINGS;Result:
+---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 1265 | Data truncated for column 'ProductName' at row 4 | +---------+------+--------------------------------------------------+
The data in our ProductName column at row 4 has been truncated. In our case, this isn’t a problem – you’ll recall that this contained NULL.
Let’s take a look at the data anyway:
SELECT * FROM Products;Result:
+-----------+-------------+--------------------+-------+ | ProductId | ProductName | ProductDescription | Price | +-----------+-------------+--------------------+-------+ | 1 | Hammer | NULL | NULL | | 2 | | NULL | NULL | | 3 | | NULL | NULL | | 4 | | NULL | NULL | +-----------+-------------+--------------------+-------+
So it no longer contains NULL (but it doesn’t contain our new default value N/A either).
Anyway, let’s insert another row:
INSERT INTO Products ( ProductId )
VALUES ( 5 );Result:
Query OK, 1 row affected (0.00 sec)
And let’s check the data:
SELECT * FROM Products;Result:
+-----------+-------------+--------------------+-------+ | ProductId | ProductName | ProductDescription | Price | +-----------+-------------+--------------------+-------+ | 1 | Hammer | NULL | NULL | | 2 | | NULL | NULL | | 3 | | NULL | NULL | | 4 | | NULL | NULL | | 5 | N/A | NULL | NULL | +-----------+-------------+--------------------+-------+
This time the default value of N/A was inserted into the ProductName column, as expected.
So Which Solution to Use?
The examples on this page are just that – examples. In the real world, columns like the ProductName column shouldn’t really be left blank or set to a generic default value. Such columns are usually set to NOT NULL for good reason, and overriding this would compromise data integrity. Imagine if you had a database full of products with no name or with the same (default) name. Therefore, solution 1 above (i.e. insert an explicit value for the column) will usually be the best solution for such columns.
However, if a business rule states that there should be a default setting, then that could be a good time to explicitly implement a default clause.
And if the column is more of an optional column, it may be fine for it to be nullable and/or to have a default value.