How to Fix ORA-01400: Cannot Insert NULL in Oracle

ORA-01400 means an INSERT left a NOT NULL column without a value, or gave it NULL explicitly. The message names the schema, table, and column, so the only question is why the value was missing: the column was left out of the INSERT, the application sent an empty value, or a default did not apply. The fix is to supply the value or give the column a default that covers the case.

Reproduce the Error

Example:

create table demo_tickets (
  ticket_id number generated always as identity primary key,
  title     varchar2(60) not null,
  status    varchar2(10) default 'OPEN' not null
);
insert into demo_tickets (status) values ('OPEN');
insert into demo_tickets (title, status) values ('Printer jam', null);

Output:

Table DEMO_TICKETS created.

Error starting at line : 6
In command -
insert into demo_tickets (status) values ('OPEN')
Error report -
ORA-01400: cannot insert NULL into ("NIMBUS"."DEMO_TICKETS"."TITLE")

Error starting at line : 7
In command -
insert into demo_tickets (title, status) values ('Printer jam', null)
Error at Command Line : 7 Column : 65
Error report -
SQL Error: ORA-01400: cannot insert NULL into ("NIMBUS"."DEMO_TICKETS"."STATUS")

• The first INSERT leaves out title, which has no default.
• The second gives status an explicit NULL. A normal DEFAULT applies only when the column is left out, not when NULL is sent.

Fix: Supply the Value, or Default on NULL

Example:

insert into demo_tickets (title) values ('Printer jam');                 -- status takes its default
-- DEFAULT ON NULL also covers an explicit NULL
alter table demo_tickets modify status default on null 'OPEN';
insert into demo_tickets (title, status) values ('Screen flickers', null);
select ticket_id, title, status from demo_tickets order by ticket_id;

Output:

1 row inserted.

Table DEMO_TICKETS altered.

1 row inserted.

   TICKET_ID TITLE              STATUS
____________ __________________ _________
           3 Printer jam        OPEN
           4 Screen flickers    OPEN

DEFAULT ON NULL makes the default apply to an explicit NULL as well, so the second ticket also gets OPEN. The ticket IDs start at 3 because the identity values used by the failed inserts are not reused.

Things to Know

• Applications that send every column, empty or not, need DEFAULT ON NULL rather than DEFAULT.
• An empty string '' is NULL in Oracle, so it also raises ORA-01400 for a NOT NULL column.
• For updates that set a NOT NULL column to NULL, the error is ORA-01407.

Related Guides

• How to Create Identity Columns in Oracle
• How NULL Works in Oracle Conditions

Conclusion

ORA-01400 names the NOT NULL column that received no value. Include it in the INSERT, or define DEFAULT ON NULL so that missing and explicit NULL values both get the default.