ERROR 1364 (HY000): Field doesn't have a default value

What it means

When an INSERT leaves a column out, the server fills it with the column’s DEFAULT, or NULL if the column allows it. A column declared NOT NULL without a DEFAULT has neither, so in strict mode (the default since MySQL 5.7, and in MariaDB since 10.2.4) the server refuses the row with error 1364, naming the first such column. Nothing is stored.

It’s the partner of “Column cannot be null”: 1364 is about a column you left out; 1048 about a NULL you sent.

Common causes

1. A column added to the table (NOT NULL, no default) that older code doesn’t know about, so its INSERT statements leave it out.
2. Code written for a server without strict mode, such as MySQL 5.6, which quietly stored 0, '' or a zero date instead, and now fails after an upgrade or on a new server.
3. created_at and updated_at columns that the application or framework is expected to fill, but a script or a raw INSERT doesn’t.
4. An INSERT … SELECT whose column list leaves out a required column.

How to fix it

Send a value

Add the column to the INSERT:

INSERT INTO orders (customer_id, total, created_at) VALUES (1, 9.99, NOW());

Give the column a default

When there’s a sensible default, put it in the table so every INSERT gets it:

ALTER TABLE orders MODIFY created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE orders MODIFY status varchar(20) NOT NULL DEFAULT 'new';

MODIFY replaces the whole definition, so restate the type and NOT NULL. On MySQL 8, a default can also be an expression in brackets, such as DEFAULT (UUID()).

Or allow NULL

If the value really can be unknown:

ALTER TABLE orders MODIFY customer_id int NULL;

A trigger can fill it in

A BEFORE INSERT trigger that sets the column counts: the check happens after the trigger has run.

CREATE TRIGGER slugs_bi BEFORE INSERT ON slugs
FOR EACH ROW SET NEW.slug = LOWER(REPLACE(NEW.title, ' ', '-'));

With that trigger, INSERT INTO slugs (title) VALUES ('Hello World') stored hello-world in the NOT NULL slug column. (Creating triggers on a server with binary logging may need extra privileges; see error 1418.)

Don’t switch off strict mode

Removing STRICT_TRANS_TABLES from sql_mode makes the error a warning, and the row gets the type’s implicit default: 0, an empty string, or 0000-00-00 00:00:00 for a DATETIME. Those values then look like real data, and the zero date causes other errors later.

Reproduce it

On MySQL 8.4.11, with orders (id int AUTO_INCREMENT PRIMARY KEY, customer_id int NOT NULL, total decimal(10,2) NOT NULL, created_at datetime NOT NULL):

INSERT INTO orders (customer_id, total) VALUES (1, 9.99);
INSERT INTO orders (total) VALUES (9.99);
INSERT INTO orders () VALUES ();

ERROR 1364 (HY000): Field 'created_at' doesn't have a default value
ERROR 1364 (HY000): Field 'customer_id' doesn't have a default value
ERROR 1364 (HY000): Field 'customer_id' doesn't have a default value

With sql_mode = '', the second statement stored a row with customer_id 0 and created_at 0000-00-00 00:00:00, and two warnings:

Warning	1364	Field 'customer_id' doesn't have a default value
Warning	1364	Field 'created_at' doesn't have a default value

After ALTER TABLE orders MODIFY created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, the first statement worked. A generated column didn’t help the column it’s computed from: INSERT INTO gen (id) into gen (id, a int NOT NULL, b int AS (a * 2)) failed with Field 'a' doesn't have a default value.

MariaDB 11.4.13 gave the same errors, warnings and results, trigger included.

In Inlet

When you insert a row in Inlet’s grid, the edit is staged until you commit, and Review shows the exact INSERT first, so you can see which columns it sets. If the server refuses it, Inlet shows the error and links to this page; the structure editor adds a default to a column and shows the DDL before it runs.