Ошибка 1364 mysql workbench

In this article, we will discuss why Error 1364 occurs and how to resolve it. 

Table of Contents

• Introduction
• Error code 1364 resolution with AUTO_INCREMENT
• Error code 1364 resolution with DEFAULT value

Introduction

MySQL server throws the Error 1364 if the query or statement tries to insert a row without a value for a particular column defined as NOT NULL. We can say that the absence of a NOT NULL column value during insertion causes this error to be thrown by the MySQL server. 

Error 1364  indicates that the value of the particular field should be something other than NULL. One way to resolve the error forever is to make the column as DEFAULT NULL in table definition but if that does not meet your requirement, let us see some ways to fix this error in the below sections.

We will be creating a sample table employee_details for illustration of the concept.

#create the table employee_details
 CREATE TABLE employee_details(
  emp_id int ,
  emp_enroll_no varchar(255) NOT NULL,
  emp_firstName varchar(255) DEFAULT NULL,
  emp_lastName varchar(255) DEFAULT NULL,
  primary key(emp_id)
);

Here, the column emp_id and emp_enroll_no both cannot be NULL.

DESC employee_details;

Output:-

Error code 1364 resolution with AUTO_INCREMENT

In this section, we will recreate error 1364 and will fix it using the AUTO_INCREMENT keyword. AUTO_INCREMENT in MySQL assigns a numeric value to a column starting from 1 (when another starting number not specified) and then increments the value by 1 for consecutive inserts.

Let us try to insert a row without specifying any value for column emp_id.

INSERT INTO employee_details (emp_enroll_no,emp_firstName,emp_lastName) VALUES("1-N","Henry","Smith");

Action Output:-

Since we did not specify any value for emp_id in the insert statement, the output in image_2 shows that the error 1364 is thrown with the message response: Error Code: 1364. Field ’emp_id’ doesn’t have a default value.

Observe the below ALTER query for the solution. Any insert happening after executing the below statement will assign a value to emp_id starting with 1 and incremented by 1 in successive inserts. 

ALTER TABLE employee_details MODIFY emp_id int NOT NULL AUTO_INCREMENT;

Action Output:-

Let us again try to execute the insert statement.

INSERT INTO employee_details (emp_enroll_no,emp_firstName,emp_lastName) VALUES("1-N","Henry","Smith");

Action Output:-

Insert is successful this time.

SELECT * FROM employee_details;

Output:-

Error code 1364 resolution with DEFAULT value

This section will recreate error 1364 and fix it by assigning a DEFAULT value to the column.

Let us try to insert a row without specifying any value for column emp_enroll_no.

 INSERT INTO employee_details (emp_id, emp_firstName, emp_lastName) VALUES(2, "Richa", "Johnson");

Action Output:-

Since we did not specify any value for emp_enroll_no in the insert statement, the output in image_6 shows that the error 1364 is thrown with the message response: Error Code: 1364. Field ’emp_enroll_no’ doesn’t have a default value.

Observe the below ALTER query for the solution. Here, we will give a default value to the column emp_enroll_no such that if any insert happens without any value for emp_enroll_no, a default value “N-N” will be inserted.

ALTER TABLE employee_details MODIFY emp_enroll_no varchar(255) NOT NULL DEFAULT "N-N";

Action Output:-

Let us again try to execute the same insert statement.

INSERT INTO employee_details (emp_id, emp_firstName, emp_lastName) VALUES(2, "Richa", "Johnson");

Action Output:-

Insert is successful this time.

SELECT * FROM employee_details;

Output:-

The output in image_9 shows that a default value of “N-N” was inserted in second row.

READ MORE:

• MySQL: Error 1264 Out of range value for a column [Solved]

We hope this article helped you understand and resolve Error 1364 in MySQL. Good Luck!!!

MySQL Error 1364 can now be fixed with any of these methods provided in this article. At Bobcares, as part of our MySQL Support Service, we answers all MySQL inquiries, large or small.

When will we see ‘MySQL Error 1364’?

If a query or statement tries to insert/update a record without a value for a specific column that is NOT NULL, MySQL server throws the Error 1364. We can say that the MySQL server throws this error because there wasn’t a NOT NULL column value during the insertion process.

This exception only occurs when using MySQL in Strict mode. We can find out easily what mode the MySQL server is running in by executing the following command:

SELECT @@sql_mode;

How To Fix MySQL Error 1364?

Let’s see the ways our Support team suggests to fix this error.

1. Logic Solution: When trying to insert a record, the value will be null but the error won’t be thrown if you provide a value for the field that is causing the exception or specify that the field can be null. For instance, with a column that cannot be null, the following query would throw the exception.

   INSERT INTO user (column_a, column_b) VALUES ("Value First Column", NULL);

   Now we alter the column to make the column nullable.

   ALTER TABLE YourTable MODIFY column_b VARCHAR(255) DEFAULT NULL;

   The MySQL Error 1364 will not appear again.

2. Server Side Solution: Uninstalling MySQL’s strict mode is a simple solution. By changing the sql_mode setting to an empty string or by removing the STRICT_TRANS_TABLES option that puts MySQL in Strict Mode and enabling the NO_ENGINE_SUBSTITUTION mode, we can disable the strict mode in MySQL’s configuration file (my.ini in Windows or my.cnf in Linux).

   [mysqld]
   sql-mode=""
   # Or
   # sql-mode="NO_ENGINE_SUBSTITUTION"
   

   Now save the changes, restart MySQL, and the MySQL Error 1364 won’t show up again.

3. Using AUTO_INCREMENT: When no other starting number is specified, MySQL’s AUTO INCREMENT function assigns a numeric value to each column starting at 1 and increases that value by 1 with each additional insert. Let’s take an example:

   CREATE TABLE employee_details(
     emp_id int ,
     emp_enroll_no varchar(255) NOT NULL,
     emp_firstName varchar(255) DEFAULT NULL,
     emp_lastName varchar(255) DEFAULT NULL,
     primary key(emp_id)
   );

   Here the columns, emp_id and emp_enroll_no both cannot be NULL. Then insert a record with the command without specifying any value for column emp_id.

   INSERT INTO employee_details (emp_enroll_no,emp_firstName,emp_lastName) VALUES("1-N","Henry","Smith");

   Since we left emp_id blank in the insert statement, the output reveals the MySQL Error 1364 with the following message response: Error Code 1364. So we can fix it by ALTER query. Any insert after executing the below statement will assign a value to emp_id starting with 1 and increments by 1 in successive inserts.

   ALTER TABLE employee_details MODIFY emp_id int NOT NULL AUTO_INCREMENT;

   Now executing the insert statement again without specifying any value for column emp_id will result in a successful insert this time.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, Our Support team briefly explains about the MySQL Error 1364. The article also includes some of the simple solutions to fix the error.

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.