Why Does MySQL Throw Error 1364: 'Field Doesn't Have a Default Value'?

MySQL Error 1364: Field Doesn't Have Default Values

When attempting to insert data into a table without explicitly specifying a value for a non-nullable column that lacks a default value, MySQL may throw an error stating "Field doesn't have a default value" (Error No. 1364).

Problem Description

A user has encountered this error when inserting into a table with the following schema:

CREATE TABLE try (
  name VARCHAR(8),
  CREATED_BY VARCHAR(40) NOT NULL
);

A trigger was created to automatically populate the CREATED_BY field with the username:

CREATE TRIGGER autoPopulateAtInsert
BEFORE INSERT ON try
FOR EACH ROW
SET NEW.CREATED_BY = USER();

However, attempting to insert a row using the following query:

INSERT INTO try (name) VALUES ('abc');

still results in the error message.

Solution

The error message is a result of the STRICT_TRANS_TABLES SQL mode being set in the MySQL configuration file (my.ini or similar). This mode enforces strict rules regarding default values for non-nullable columns. To resolve the issue, the user should:

1. Open the MySQL configuration file.
2. Locate the STRICT_TRANS_TABLES setting and remove or disable it.
3. Restart the MySQL service.

Alternatively, the user can create a default value for the CREATED_BY field using the ALTER TABLE statement. However, this approach may not be suitable if the trigger is intended to dynamically set the value.

The above is the detailed content of Why Does MySQL Throw Error 1364: 'Field Doesn't Have a Default Value'?. For more information, please follow other related articles on the PHP Chinese website!