How to Resolve ORA-01400: cannot insert NULL into

  • by

ORA-01400

ORA-01400 means that there's a column which was found as NOT NULL is not listed in the INSERT statement, you have to provide a value for it. For example, I tried to insert a row into a table.

SQL> conn hr/hr
Connected.
SQL> insert into hr.countries (country_id, country_name) values ('SE', 'Sweden');
insert into hr.countries (country_id, country_name) values ('SE', 'Sweden')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."COUNTRIES"."REGION_ID")

Let's describe the table's definition.

SQL> desc hr.countries;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID                                NOT NULL CHAR(2)
 COUNTRY_NAME                              NOT NULL VARCHAR2(40)
 REGION_ID                                 NOT NULL NUMBER

As you can see, the column is NOT NULL which is one type of constraints to keep data integrity.

Please note that, not only in a normal INSERT, but also in import, SQL Loader, Golden Gate, Hibernate and Informatica could see ORA-01400 sometimes. Especially for data import which includes imp of original import and impdp of data pump.

As for JDBC exception handling, sometimes the error message may not be so obvious because it's related to constraint violation signals, but fortunately the error stack that companies with ORA-01400 is mostly the same:

  • java.sql.SQLException
  • java.sql.SQLNonTransientException
  • java.sql.SQLIntegrityConstraintViolationException

Solutions to ORA-01400

Now, we can have 3 choices to solve ORA-01400.

  1. Put the Column on the List of INSERT
  2. Remove NOT NULL constraint from the Column
  3. Provide a default Value for the Column

1. Put the Column on the List of INSERT

You have to modify the statement and provide a proper value to it.

SQL> insert into hr.countries (country_id, country_name, region_id) values ('SE', 'Sweden', 1);

1 row created.

It's successful. Now we have to revert the operation for later steps.

SQL> rollback;

Rollback complete.

I know, sometimes, you don't have any proper value of this column. So you can go for the next choice.

2. Remove NOT NULL constraint from the Column

You can either disable the constraint or drop it, but the drawback is that you have to change the definition of the table, the data integrity may be compromised.

Disable the constraint

Let's check which constraint should be disabled from the column.

SQL> column constraint_name format a25;
SQL> select a.constraint_name, b.status from user_cons_columns a inner join user_constraints b on a.constraint_name = b.constraint_name where a.table_name = 'COUNTRIES' and a.column_name = 'REGION_ID' and b.constraint_type = 'C' and search_condition_vc like '%NOT NULL';

CONSTRAINT_NAME           STATUS
------------------------- --------
SYS_C007819               ENABLED

Then we disable the constraint by ALTER TABLE DISABLE CONSTRAINT.

SQL> alter table countries disable constraint SYS_C007819;

Table altered.

Check the status again.

SQL> select a.constraint_name, b.status from user_cons_columns a inner join user_constraints b on a.constraint_name = b.constraint_name where a.table_name = 'COUNTRIES' and a.column_name = 'REGION_ID' and b.constraint_type = 'C' and search_condition_vc like '%NOT NULL';

CONSTRAINT_NAME           STATUS
------------------------- --------
SYS_C007819               DISABLED

SQL> desc countries;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID                                NOT NULL CHAR(2)
 COUNTRY_NAME                              NOT NULL VARCHAR2(40)
 REGION_ID                                          NUMBER

As you can see, the constraint was removed, just temporarily.

Drop the constraint

To remove the constraint permanently, you can drop it by ALTER TABLE DROP CONSTRAINT.

SQL> alter table countries drop constraint SYS_C007819;

Table altered.

SQL> select a.constraint_name, b.status from user_cons_columns a inner join user_constraints b on a.constraint_name = b.constraint_name where a.table_name = 'COUNTRIES' and a.column_name = 'REGION_ID' and b.constraint_type = 'C';

no rows selected

Dropping a NOT NULL constraint can be easier than the above statement. I guess you'd like to know more ways to add or drop a NOT NULL constraint.

No matter you disable or drop it, you can insert the row now.

SQL> insert into hr.countries (country_id, country_name) values ('SE', 'Sweden');

1 row created.

It's successful. Now we have to revert the operation for later steps.

SQL> rollback;

Rollback complete.

SQL> alter table countries modify (region_id not null);

Table altered.

3. Provide a default Value for the Column

This could be the best solution to ORA-01400.

SQL> alter table hr.countries modify (region_id default 1);

Table altered.

SQL> insert into hr.countries (country_id, country_name) values ('SE', 'Sweden');

1 row created.

The best thing is that you don't have to modify INSERT statements or remove NOT NULL constraints from the column. The business logic stays stable.

Leave a Reply

Your email address will not be published. Required fields are marked *