Field doesn't have a default value

What this means

Error 1364 (SQLSTATE HY000) is returned when an INSERT statement omits a column that is NOT NULL and has no DEFAULT value. In strict SQL mode (STRICT_TRANS_TABLES) this is a hard error; in non-strict mode it may generate a warning and insert the implicit default for the type.

Why it happens

1. 1INSERT statement omits a required NOT NULL column with no DEFAULT
2. 2Application code does not include all required fields in the INSERT
3. 3Schema changed (column added as NOT NULL with no DEFAULT) but application was not updated

How to reproduce

Inserting a row without a required column.

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50) NOT NULL  -- no DEFAULT
);
INSERT INTO employees (name) VALUES ('Ana');
-- 'department' is required

Fix 1: Include the missing column in the INSERT

When the value is known at insert time.

INSERT INTO employees (name, department) VALUES ('Ana', 'Engineering');

Fix 2: Add a DEFAULT value to the column

When a sensible default exists for the column.

ALTER TABLE employees
  MODIFY department VARCHAR(50) NOT NULL DEFAULT 'Unassigned';

Fix 3: Make the column nullable

When the value may legitimately be unknown at insert time.

ALTER TABLE employees
  MODIFY department VARCHAR(50) NULL DEFAULT NULL;

What not to do

Sources

Confidence assessment

See also