ORA-01400: cannot insert NULL into column

ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE"."COLUMN")

An INSERT left a NOT NULL column empty. The message names the column. Supply a value, give the column a default, or let a trigger or identity column fill it.

What it means

A NOT NULL constraint means every row must have a value in that column. If an INSERT does not mention the column and the column has no default — or explicitly inserts NULL — Oracle rejects the row and names the column, for example ("HR"."EMPLOYEES"."HIRE_DATE").

Common causes

• The column is not in the INSERT column list and has no default value.
• The application sends NULL or an empty string (Oracle treats '' as NULL).
• An INSERT … SELECT where the source column contains NULLs.
• A primary key expected to be filled by a sequence, trigger or identity column that is missing.

How to fix it

1. Read the column name in the message and add it to the INSERT with a value.
2. Give the column a default: ALTER TABLE t MODIFY (col DEFAULT …), or DEFAULT ON NULL on Oracle 12c and later to replace explicit NULLs too.
3. For keys, use an identity column (12c and later) or sequence.NEXTVAL.
4. Remember that an empty string is NULL in Oracle.

Example

Fill the column even when NULL is sent (12c and later):

ALTER TABLE orders MODIFY (status DEFAULT ON NULL 'NEW');

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Related errors

ORA-00001ORA-12899ORA-01722 All Oracle errors →

Oracle documentation for ORA-01400. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.