MySQL: ERROR 1048 (23000): Column cannot be null

What it means

A column declared NOT NULL must always hold a value. Error 1048 means a statement tried to store NULL in one, so the server rejected the statement; nothing it would have changed is kept. The message names the column.

It’s the companion of “Field doesn’t have a default value”: that one is about a NOT NULL column you left out; this one is about a NULL you sent.

One rule catches people out: a column’s DEFAULT is used only when you leave the column out of the INSERT (or write DEFAULT). An explicit NULL isn’t “no value”, so a column declared NOT NULL DEFAULT 'active' still rejects INSERT … VALUES (NULL).

Common causes

The application sends NULL for a missing value: an empty form field, an unset property that an ORM writes as NULL instead of leaving out, or a variable that was never filled.
INSERT … SELECT or a join that produces NULL for rows with no match, such as a LEFT JOIN column copied into a NOT NULL column.
UPDATE … SET col = NULL, often from a subquery that found no row.
Expecting DEFAULT CURRENT_TIMESTAMP to fill in a NULL. On MySQL 8 a TIMESTAMP NOT NULL column rejects NULL like any other (explicit_defaults_for_timestamp is on). MariaDB 11.4 still stores the current time instead.

How to fix it

Find which value is NULL

The message names the column. Check what your code binds to it, and for INSERT … SELECT, run the SELECT alone with a filter on that expression:

SELECT o.id, u.email
FROM orders o LEFT JOIN users u ON u.id = o.user_id
WHERE u.email IS NULL;

Leave the column out, or write DEFAULT

To get the column’s default, don’t mention it, or say so:

INSERT INTO accounts (name) VALUES ('Ann');                 -- status gets its default
INSERT INTO accounts (name, status) VALUES ('Ann', DEFAULT);
INSERT INTO accounts (name, status) VALUES ('Ann', COALESCE(?, DEFAULT(status)));

The last form uses the bound value when there is one and the default otherwise, which suits an application that may or may not have a value.

Allow NULL, if the value really can be missing

ALTER TABLE customers MODIFY email varchar(100) NULL;

MODIFY replaces the whole column definition, so restate its type, default and comment. Changing nullability can rebuild the table; see changing a column type.

Making a column NOT NULL that already has NULLs

The ALTER TABLE fails until every row has a value, with a different error: 1138 Invalid use of NULL value on MySQL 8.4, 1265 Data truncated on MariaDB 11.4. Fill the gaps first:

UPDATE accounts SET nick = '' WHERE nick IS NULL;
ALTER TABLE accounts MODIFY nick varchar(20) NOT NULL;

Don’t switch off strict mode to hide it

Without strict mode, a multi-row INSERT stores the column type’s implicit default instead (an empty string, 0, or a zero date) and only warns. So does INSERT IGNORE, in any mode. The row goes in with a value nobody chose, which is usually worse than the error.

Reproduce it

On MySQL 8.4.11, with customers (id int AUTO_INCREMENT PRIMARY KEY, email varchar(100) NOT NULL, name varchar(100)):

INSERT INTO customers (email, name) VALUES (NULL, 'Ann');
UPDATE customers SET email = NULL WHERE id = 1;
INSERT INTO customers (email, name) VALUES ('a@x', 'A'), (NULL, 'B');
INSERT INTO customers (email, name) SELECT NULL, 'C';

ERROR 1048 (23000): Column 'email' cannot be null
ERROR 1048 (23000): Column 'email' cannot be null
ERROR 1048 (23000): Column 'email' cannot be null
ERROR 1048 (23000): Column 'email' cannot be null

The multi-row insert stored neither row. A column with a default behaved the same: status varchar(10) NOT NULL DEFAULT 'active' rejected VALUES (NULL), while VALUES (DEFAULT), leaving it out, and COALESCE(NULL, DEFAULT(status)) all stored active. So did seen_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP:

ERROR 1048 (23000): Column 'status' cannot be null
ERROR 1048 (23000): Column 'seen_at' cannot be null

INSERT IGNORE … VALUES (NULL, 'D') stored an empty email with Warning 1048. With sql_mode = '' the single-row insert still failed, but the two-row one stored '' for the NULL with a warning.

MariaDB 11.4.13 gave the same results and messages, except that it stored the current time for NULL in the TIMESTAMP NOT NULL column.

In Inlet

In Inlet’s grid, a cell can be set to NULL explicitly, and edits are staged until you commit, with Review showing the exact SQL first. If the server rejects a NULL, Inlet shows the error and links to this page; the structure editor changes a column’s nullability and shows the DDL before it runs.

ERROR 1048 (23000): Column cannot be null