How to Resolve ORA-01400: cannot insert NULL into

  • by

ORA-01400

ORA-01400 tells us that there’s a column which is not listed in the INSERT statement was found as NOT NULL, you have to provide a value to 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.

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
  3. Provide a default Value

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

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

This could be the best solution to ORA-01400. You don’t have to modify statements or remove NOT NULL constraints. The business logic stays stable.

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.

Leave a Reply

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