ORA-01400: cannot insert NULL into (column)

ORA-01400: cannot insert NULL into ("LAB_A"."MEMBERS"."STATUS")

An INSERT tried to put NULL into a column that is NOT NULL.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-11

ORA-01400 names the column directly: ("LAB_A"."MEMBERS"."STATUS") is schema, table and column. That column is NOT NULL (primary key columns are always NOT NULL) and the INSERT gave it no value.

The question is why the value was NULL: the column was missing from the INSERT, nothing generates the key, or the source data really contains NULL. A DEFAULT only helps in the first case.

Causes and fixes

  1. A NOT NULL column is left out of the INSERT and has no default
  2. The primary key is not given and nothing generates it
  3. The column has a DEFAULT, but the INSERT passes NULL explicitly

1A NOT NULL column is left out of the INSERT and has no default

The INSERT lists only member_id and name. STATUS is not listed, so it gets its default value, and there is none: USER_TAB_COLUMNS shows NULLABLE = N and an empty DATA_DEFAULT.

After adding a default with ALTER TABLE ... MODIFY status DEFAULT 'ACTIVE', the same INSERT succeeds without changes.

Setup
Setuprun as lab_a
CREATE TABLE members (member_id NUMBER PRIMARY KEY, name VARCHAR2(50) NOT NULL, status VARCHAR2(10) NOT NULL);
OUTPUT
Table created.
Reproducerun as lab_a
INSERT INTO members (member_id, name) VALUES (1, 'Kim');
OUTPUT
INSERT INTO members (member_id, name) VALUES (1, 'Kim')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("LAB_A"."MEMBERS"."STATUS")
Help: https://docs.oracle.com/error-help/db/ora-01400/
Checkrun as lab_a
SELECT column_name, nullable, data_default FROM user_tab_columns WHERE table_name = 'MEMBERS' ORDER BY column_id;
OUTPUT
COLUMN_NAME                    N DATA_DEFAULT
------------------------------ - --------------------------------------------------------------------------------
MEMBER_ID                      N
NAME                           N
STATUS                         N

3 rows selected.
Fixrun as lab_a
ALTER TABLE members MODIFY status DEFAULT 'ACTIVE';
OUTPUT
Table altered.

Add a default only if one value really fits every new row. Otherwise the INSERT must supply the value.

After the fixrun as lab_a
INSERT INTO members (member_id, name) VALUES (1, 'Kim');
OUTPUT
1 row created.

2The primary key is not given and nothing generates it

Many developers expect the ID to be filled automatically, but a plain NUMBER PRIMARY KEY column has no generator. Leaving it out of the INSERT means NULL, and a primary key cannot be NULL.

Here a sequence was created and the INSERT takes the key from orders_seq.NEXTVAL.

Setup
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, amount NUMBER);
OUTPUT
Table created.
Reproducerun as lab_a
INSERT INTO orders (amount) VALUES (500);
OUTPUT
INSERT INTO orders (amount) VALUES (500)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("LAB_A"."ORDERS"."ORDER_ID")
Help: https://docs.oracle.com/error-help/db/ora-01400/
Fixrun as lab_a
CREATE SEQUENCE orders_seq;
OUTPUT
Sequence created.
After the fixrun as lab_a
INSERT INTO orders (order_id, amount) VALUES (orders_seq.NEXTVAL, 500);
OUTPUT
1 row created.

3The column has a DEFAULT, but the INSERT passes NULL explicitly

STATUS has DEFAULT 'ACTIVE', yet INSERT ... SELECT from the staging table fails. A plain DEFAULT is used only when the column is left out. Here the column is listed and one source row (Lee) has NULL, so NULL is inserted. Note the error points at line 2, the SELECT that supplies the values.

DEFAULT ON NULL also replaces explicit NULLs. After the change, both rows are inserted and Lee gets ACTIVE.

Setup
Setuprun as lab_a
CREATE TABLE members (member_id NUMBER PRIMARY KEY, name VARCHAR2(50), status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL);
CREATE TABLE members_stage (member_id NUMBER, name VARCHAR2(50), status VARCHAR2(10));
INSERT INTO members_stage VALUES (1, 'Kim', 'ACTIVE');
INSERT INTO members_stage VALUES (2, 'Lee', NULL);
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
INSERT INTO members (member_id, name, status)
SELECT member_id, name, status FROM members_stage;
OUTPUT
SELECT member_id, name, status FROM members_stage
                        *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("LAB_A"."MEMBERS"."STATUS")
Help: https://docs.oracle.com/error-help/db/ora-01400/
Fixrun as lab_a
ALTER TABLE members MODIFY status DEFAULT ON NULL 'ACTIVE';
OUTPUT
Table altered.
After the fixrun as lab_a
INSERT INTO members (member_id, name, status)
SELECT member_id, name, status FROM members_stage;
SELECT * FROM members ORDER BY member_id;
OUTPUT
2 rows created.


 MEMBER_ID NAME                           STATUS
---------- ------------------------------ ------------------------------
         1 Kim                            ACTIVE
         2 Lee                            ACTIVE

2 rows selected.

FAQ

How do I find which column causes ORA-01400?

The message names it in the form ("SCHEMA"."TABLE"."COLUMN"). The last part is the column.

I set a DEFAULT but still get ORA-01400. Why?

The INSERT is passing NULL explicitly (for example from INSERT ... SELECT or a bind variable). A plain DEFAULT applies only when the column is omitted. Use DEFAULT ON NULL, or leave the column out of the INSERT.

What is the difference between ORA-01400 and ORA-01407?

ORA-01400 comes from an INSERT. Setting a NOT NULL column to NULL in an UPDATE gives ORA-01407 (cannot update (...) to NULL) instead.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors