# ERROR 1048: Column Cannot Be Null in MySQL

Source: https://www.bytebase.com/reference/mysql/error/1048-column-cannot-be-null/

---

## Error Message

```sql
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](/reference/mysql/error/1364-field-doesnt-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:

```sql
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.

```sql
-- Application: always populate email before save
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- 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:

```sql
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:

```sql
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
(name, @email)
SET email = IF(@email = '' OR @email IS NULL, 'unknown@example.com', @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`.

```sql
-- 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](/reference/mysql/error/1364-field-doesnt-have-a-default-value).)

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

```sql
-- 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](https://docs.bytebase.com/sql-review/review-rules/) 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:

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

```sql
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:

```ruby
class UserSetting < ApplicationRecord
    before_validation :default_preferences

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

### INSERT ... SELECT from a nullable source

```sql
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:

```sql
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.

```sql
-- 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.

```sql
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:

```sql
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:

```sql
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

> **Note:** [Bytebase's SQL Review](https://docs.bytebase.com/sql-review/review-rules/) flags `ADD COLUMN ... NOT NULL` migrations without a `DEFAULT` before they ship, the single most common 1048 source in production schemas. See also [ERROR 1364: Field Doesn't Have a Default Value](/reference/mysql/error/1364-field-doesnt-have-a-default-value) and [ERROR 1292: Truncated Incorrect Value](/reference/mysql/error/1292-truncated-incorrect-value) — both are STRICT mode siblings of 1048.