Error Code: 1364 [Solved] Field doesn’t have a default value

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!!!