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
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
CREATE TABLE members (member_id NUMBER PRIMARY KEY, name VARCHAR2(50) NOT NULL, status VARCHAR2(10) NOT NULL);Table created.INSERT INTO members (member_id, name) VALUES (1, 'Kim');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/SELECT column_name, nullable, data_default FROM user_tab_columns WHERE table_name = 'MEMBERS' ORDER BY column_id;COLUMN_NAME N DATA_DEFAULT
------------------------------ - --------------------------------------------------------------------------------
MEMBER_ID N
NAME N
STATUS N
3 rows selected.ALTER TABLE members MODIFY status DEFAULT 'ACTIVE';Table altered.Add a default only if one value really fits every new row. Otherwise the INSERT must supply the value.
INSERT INTO members (member_id, name) VALUES (1, 'Kim');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
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, amount NUMBER);Table created.INSERT INTO orders (amount) VALUES (500);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/CREATE SEQUENCE orders_seq;Sequence created.INSERT INTO orders (order_id, amount) VALUES (orders_seq.NEXTVAL, 500);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
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;Table created.
Table created.
1 row created.
1 row created.
Commit complete.INSERT INTO members (member_id, name, status)
SELECT member_id, name, status FROM members_stage;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/ALTER TABLE members MODIFY status DEFAULT ON NULL 'ACTIVE';Table altered.INSERT INTO members (member_id, name, status)
SELECT member_id, name, status FROM members_stage;
SELECT * FROM members ORDER BY member_id;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