// Oracle error reference

ORA-01400: cannot insert NULL into column

An INSERT left a NOT NULL column empty or set it to NULL. The message names the column as (SCHEMA.TABLE.COLUMN).

Error code
ORA-01400
Category
Constraints and keys
Error message
cannot insert NULL into (string)

What it means

ORA-01400 means a row would have NULL in a column declared NOT NULL, for example ORA-01400: cannot insert NULL into ("SALES"."ORDERS"."CUSTOMER_ID"). Leaving a column out of an INSERT gives it NULL unless it has a default. Oracle also treats an empty string '' as NULL, which surprises people coming from other databases. Updating a NOT NULL column to NULL raises the related ORA-01407.

Common causes

  • The INSERT column list leaves out a required column that has no default.
  • An empty string '' inserted into a NOT NULL VARCHAR2 column; Oracle stores '' as NULL.
  • A primary key column that should be filled by a sequence or identity but isn't.

How to fix it

  1. Supply a value for the column named in the message, or give it a default.

    ALTER TABLE orders MODIFY (status DEFAULT 'NEW');
  2. Use an identity column (12c and later) or a sequence default for keys.

    CREATE TABLE orders (
        id          NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
        customer_id NUMBER NOT NULL
    );
  3. Store a placeholder such as a single space, or allow NULL, when a value can legitimately be empty.

Related errors

More constraints and keys errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.