📋

Description

MySQL Error 1364 occurs when you attempt to insert a new row into a table but fail to provide a value for a column that is defined as `NOT NULL` and does not have an explicit `DEFAULT` value specified in its schema. This error prevents the insertion of the row, as the database cannot determine what value to use for the missing field.

💬

Error Message

Field '%s' doesn't have a default value

🔍

Known Causes

4 known causes

⚠️

Missing Column Value in INSERT

You are trying to insert data into a table without explicitly providing a value for a column that is defined as `NOT NULL` and has no `DEFAULT` clause.

⚠️

Undefined DEFAULT for NOT NULL Column

The table schema defines a column as `NOT NULL` but does not include a `DEFAULT` value, meaning MySQL requires an explicit value for that column upon insertion.

⚠️

Strict SQL Mode Enabled

With strict SQL modes enabled (e.g., `STRICT_TRANS_TABLES`), MySQL will not automatically insert implicit default values (like `NULL` or `0`) for missing `NOT NULL` columns, leading to this error.

⚠️

Incorrect INSERT ... SELECT Statement

When using `INSERT ... SELECT`, the `SELECT` statement might not be returning a value for a `NOT NULL` column that lacks a `DEFAULT` value, causing the insertion to fail.

🛠️

Solutions

4 solutions available

1. Add a DEFAULT value to the column easy

Modify the table schema to specify a default value for the problematic column.

1

Identify the table and the column that is causing the error. The error message will usually specify this (e.g., 'Field 'your_column_name' doesn't have a default value').

2

Use the ALTER TABLE statement to add a DEFAULT constraint to the column. Replace `your_table_name`, `your_column_name`, and `your_default_value` with your specific details. Choose a default value appropriate for the column's data type (e.g., 0 for INT, '' for VARCHAR, CURRENT_TIMESTAMP for DATETIME).

ALTER TABLE your_table_name MODIFY COLUMN your_column_name data_type DEFAULT your_default_value;

3

If you are unsure of the exact data type, you can first check it using DESCRIBE.

DESCRIBE your_table_name;

2. Provide a value for the missing column during INSERT/UPDATE easy

Ensure that every INSERT or UPDATE statement explicitly provides a value for the column that lacks a default.

1

Examine your application code or SQL scripts that perform INSERT or UPDATE operations on the table in question.

2

For `INSERT` statements, make sure to include the column name in the column list and provide a corresponding value in the `VALUES` clause. If you are using `INSERT ... SELECT`, ensure the `SELECT` statement returns a value for that column.

INSERT INTO your_table_name (column1, your_column_name, column3) VALUES (value1, 'some_value', value3);

3

For `UPDATE` statements, include the column in the `SET` clause with a value.

UPDATE your_table_name SET column1 = value1, your_column_name = 'updated_value' WHERE condition;

3. Allow NULL values for the column easy

Modify the column definition to permit NULL values, effectively bypassing the need for a default.

1

Identify the table and the column causing the error.

2

Use the ALTER TABLE statement to change the column definition to allow NULL values. Replace `your_table_name`, `your_column_name`, and `data_type` with your specific details.

ALTER TABLE your_table_name MODIFY COLUMN your_column_name data_type NULL;

3

If the column was previously defined as NOT NULL, this change will allow it to accept NULL. Ensure that your application logic can handle NULL values for this column.

4. Add a NOT NULL constraint with a default value during table creation medium

When creating a new table, define columns with a default value and a NOT NULL constraint to prevent this error from occurring in the future.

1

When defining your `CREATE TABLE` statement, for columns that should not be NULL and do not have an explicit value provided during insertion, specify a `DEFAULT` value.

CREATE TABLE your_new_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 VARCHAR(255) NOT NULL,
    your_column_name INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2

This ensures that even if you forget to provide a value for `your_column_name` during an `INSERT`, it will automatically be set to `0`. Similarly, `created_at` will be populated with the current timestamp.

🔗

Related Errors

5 related errors

MySQL Error 1364: Field Missing Default Value