ERROR 1048: Column Cannot Be Null in MySQL

Error Message#

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

You'll see this error whenever an INSERT or UPDATE statement explicitly tries to write NULL into a column that was declared NOT NULL. With strict sql_mode (default since MySQL 5.7), MySQL refuses the write and returns 1048. Without strict mode, MySQL silently substitutes a default value (empty string, 0, or the column's DEFAULT) — quietly corrupting data instead of failing loud.

What Triggers This Error#

1048 fires only when SQL NULL is explicitly written to a NOT NULL column. A statement that omits the column entirely fires the related but different ERROR 1364: Field doesn't have a default value — useful to know because ORMs vary in which form they emit.

The most common triggers for 1048 specifically:

• ORM serializes all columns and passes NULL for unset attributes — Rails ActiveRecord, Django, and Hibernate emit INSERT INTO t (a, b, c) VALUES (?, ?, ?) with NULL bound for unset fields, which is the 1048 path. ORMs and query builders that emit a partial column list (Knex, raw SQL builders) hit 1364 instead.
• Migration adds a NOT NULL column to a table with existing rows — the implicit backfill is NULL, which violates the new constraint
• JSON null field deserialized as SQL NULL — Rails, Django, and Hibernate all do this by default
• INSERT ... SELECT from a source where the column may be NULL — the source table allows NULL, the destination doesn't
• Trigger or generated column produces NULL when source columns are missing — the trigger logic returns NULL, and the target column doesn't accept it
• ON DUPLICATE KEY UPDATE references a column that the new row didn't include — VALUES(col) returns NULL when col wasn't in the insert column list, fires 1048 if the column is NOT NULL

Fix by Scenario#

ORM passes explicit NULL for an unset attribute#

The most common 1048 path in production. An ORM call like User.create(name: 'Alice') looks like it omits email — but Rails ActiveRecord, Django, and Hibernate all serialize every mapped column and bind NULL for whatever the application didn't set. The wire-level SQL is closer to:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- What the ORM actually emits (all columns, NULL bound for email)
INSERT INTO users (name, email) VALUES ('Alice', NULL);
-- ERROR 1048: Column 'email' cannot be null

(If the ORM emits a partial column list — INSERT INTO users (name) VALUES ('Alice') — you get 1364, not 1048. Knex, jOOQ in partial mode, and most hand-written SQL builders take this path.)

Fix: either provide the value, or give the column a sensible DEFAULT. For audit columns like created_at, CURRENT_TIMESTAMP is standard. For business-required columns like email, the application should validate before the insert — don't paper over a missing email with a fake one.

-- Application: always populate email before save
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

-- Or: column gets a DEFAULT only if the empty-state makes sense
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'pending';

Bulk loads with empty / null values for NOT NULL columns#

CSV and bulk-load pipelines are a frequent source — empty cells deserialize to NULL, which NOT NULL columns reject:

LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
(name, email);
-- ERROR 1048: Column 'email' cannot be null (on rows with empty email)

Coerce the value at load time so empty cells become a sentinel instead of NULL:

LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
(name, @email)
SET email = IF(@email = '' OR @email IS NULL, '[email protected]', @email);

Better: validate the input before the load. SQL-side coercion is a fallback for dirty third-party feeds, not a primary input path.

Migration tightens a nullable column to NOT NULL with unbackfilled rows#

A common two-step migration pattern: add a column nullable first, backfill, then tighten to NOT NULL. The third step fires 1048 if any row still holds NULL.

-- Step 1 (W-1): shipped the column nullable
ALTER TABLE orders ADD COLUMN customer_id BIGINT NULL;

-- Step 2 (W-1 to W0): app code starts populating customer_id on writes,
-- but the historical backfill is incomplete

-- Step 3 (this week): try to enforce NOT NULL
ALTER TABLE orders MODIFY customer_id BIGINT NOT NULL;
-- ERROR 1048: Column 'customer_id' cannot be null

The MODIFY scans the table and sees existing NULL rows — exactly the case 1048 is designed to catch. (Adding a brand-new column with NOT NULL and no DEFAULT is the related but different ERROR 1364.)

Fix: verify every row is backfilled before the tightening step, and run a final sweep just before:

-- Verify before tightening — should return 0
SELECT COUNT(*) FROM orders WHERE customer_id IS NULL;

-- If non-zero, finish the backfill first
UPDATE orders o
SET customer_id = (SELECT id FROM customers c WHERE c.email = o.customer_email)
WHERE customer_id IS NULL;

-- Now safe to tighten
ALTER TABLE orders MODIFY customer_id BIGINT NOT NULL;

For very large tables this MODIFY rewrites the table — use online schema-change tooling (gh-ost, pt-online-schema-change, or Bytebase's online migration) if downtime is a concern.

Bytebase's SQL Review flags MODIFY ... NOT NULL migrations during change review and can run the SELECT COUNT(*) WHERE col IS NULL precondition automatically as part of the change request — catching the incomplete-backfill case before the migration runs.

JSON null deserialized as SQL NULL#

Application frameworks default to mapping JSON null to SQL NULL. If the receiving column is NOT NULL, every payload with a missing field fails:

{ "user_id": 1, "preferences": null }

INSERT INTO user_settings (user_id, preferences) VALUES (1, NULL);
-- ERROR 1048: Column 'preferences' cannot be null

Fix at the application layer. Either map JSON null to a sentinel value ({}, empty string, 0 depending on the column type), or make the column nullable. Database-level coercion is harder to reason about than a one-line application change.

In Rails:

class UserSetting < ApplicationRecord
    before_validation :default_preferences

    private
    def default_preferences
        self.preferences ||= {}
    end
end

INSERT ... SELECT from a nullable source#

INSERT INTO active_users (id, email)
SELECT id, email FROM users WHERE last_seen > NOW() - INTERVAL 30 DAY;
-- ERROR 1048: Column 'email' cannot be null

The source users.email allows NULL; the destination active_users.email doesn't. Either filter at the source, or coalesce to a sentinel:

INSERT INTO active_users (id, email)
SELECT id, email FROM users
WHERE last_seen > NOW() - INTERVAL 30 DAY
  AND email IS NOT NULL;

Add email IS NOT NULL to the source query — usually clearer than COALESCE(email, '') because the empty string isn't a meaningful email value either.

Bulk upsert where some new rows hold NULL for a NOT NULL column#

A common upsert pattern: bulk-insert from the application with ON DUPLICATE KEY UPDATE. If any row in the batch has NULL for a NOT NULL column and that row hits a duplicate key, the UPDATE clause propagates the NULL and fires 1048.

-- Modern alias syntax (MySQL 8.0.20+)
INSERT INTO products (id, name, stock, updated_by)
VALUES
    (1, 'Widget', 10, 'alice'),
    (2, 'Gadget',  5, NULL)        -- this row has NULL for updated_by
AS new
ON DUPLICATE KEY UPDATE
    stock      = new.stock,
    updated_by = new.updated_by;
-- ERROR 1048: Column 'updated_by' cannot be null
-- (fires when row 2 collides on id and the UPDATE tries updated_by = NULL)

Fix: coalesce NULL to a sentinel in the UPDATE clause, or filter the batch upstream so no row carries NULL for NOT NULL columns.

INSERT INTO products (id, name, stock, updated_by) VALUES (...)
AS new
ON DUPLICATE KEY UPDATE
    stock      = new.stock,
    updated_by = COALESCE(new.updated_by, updated_by);  -- keep existing value if new is NULL

(On MySQL versions before 8.0.20, the equivalent uses the deprecated VALUES() function: updated_by = COALESCE(VALUES(updated_by), updated_by). Prefer the alias syntax on new code — VALUES() was deprecated in 8.0.20.)

The misleading framing to avoid: when a column is omitted from the INSERT column list entirely, VALUES(col) / new.col returns the column's DEFAULT, not NULL — so omitting a column doesn't reliably trigger 1048. The 1048 path is when the row's NULL is explicit.

Checking for silent corruption (non-strict mode)#

If your sql_mode is non-strict, 1048 never surfaces — MySQL silently substitutes a default. Find these silent corruptions with SHOW WARNINGS after every batch:

INSERT INTO users (name) VALUES ('Alice');
-- Query OK, 1 row affected, 1 warning (0.00 sec)

SHOW WARNINGS;
-- +---------+------+-----------------------------+
-- | Level   | Code | Message                     |
-- +---------+------+-----------------------------+
-- | Warning | 1048 | Column 'email' cannot be null |
-- +---------+------+-----------------------------+

For ongoing data quality, enable strict mode in production:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';

The default since MySQL 5.7 already includes STRICT_TRANS_TABLES — but legacy environments often disable it to avoid breaking old code, and then accumulate years of NULL-to-default substitution.

Prevention#

• Define a DEFAULT on every NOT NULL column where an empty-state is meaningful (status DEFAULT 'pending', created_at DEFAULT CURRENT_TIMESTAMP)
• For business-required fields like email, validate at the application layer — the database error means the application already failed
• Use Pattern B (add nullable, backfill, enforce NOT NULL) for ALTER TABLE ... ADD COLUMN ... NOT NULL on large tables
• Map JSON null to a sentinel at the deserialization layer, not at the SQL layer
• Keep STRICT_TRANS_TABLES enabled in production — silent default-substitution is harder to find than 1048 errors
• After every bulk insert, run SHOW WARNINGS to catch nullability issues that didn't escalate to errors