MySQL error 1048: Column cannot be null
An INSERT or UPDATE put NULL into a column declared NOT NULL. Supply a value, give the column a default, or allow NULL.
- Error number
1048- Name
ER_BAD_NULL_ERROR- SQLSTATE
23000- Category
- Keys and constraints
What it means
The named column is NOT NULL, and the statement explicitly set it to NULL (or to an expression that evaluated to NULL). Omitting the column instead uses its default; if it has none, you get 1364 instead in strict mode.
Common causes
- Application code passing
nullfor a required field. - An
INSERT ... SELECTorUPDATE ... JOINwhere a lookup found no row and producedNULL. - A CSV import where empty fields map to
NULL.
How to fix it
-
Supply a value, or substitute one for
NULL.INSERT INTO customers (name, country) VALUES ('Ada', COALESCE(@country, 'GB')); -
Give the column a default and omit it from the insert.
ALTER TABLE customers MODIFY country CHAR(2) NOT NULL DEFAULT 'GB';
-
If the value really can be unknown, allow
NULL.ALTER TABLE customers MODIFY country CHAR(2) NULL;
Reproduce it safely
CREATE TEMPORARY TABLE t (a INT NOT NULL); INSERT INTO t VALUES (NULL); -- ERROR 1048 (23000): Column 'a' cannot be null
Related errors
1364Field doesn't have a default value1062Duplicate entry for key515SQL Server: Cannot insert the value NULL into column23502PostgreSQL: null value violates not-null constraintORA-01400Oracle: cannot insert NULL into columnSQLITE_CONSTRAINT_NOTNULLSQLite: NOT NULL constraint failed
More keys and constraints errors
1061Duplicate key name1452Foreign key fails: cannot add child row1451Foreign key fails: cannot delete parent row1216Cannot add or update a child row1217Cannot delete or update a parent row1215Cannot add foreign key constraint3780Foreign key columns are incompatible1822Missing index for foreign key constraint
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.