Have you gotten the “ORA-01400: cannot insert null into (string)” error? Learn what causes this and how to resolve it in this article.
ORA-01400 Cause
If you try to run an INSERT statement to insert data into a table, you may get this error.
ORA-01400: cannot insert NULL into (string)
In Oracle databases, you can store a NULL value in any column of any data type, as long as the column is not defined as “NOT NULL” or is a primary key.
A NULL value is not the same as 0 or an empty string of ”.
When you attempt to insert a record into a table, and a value of NULL is being inserted into a column that does not allow NULL values, then this error will occur.
To resolve the ORA-01400 error, you have a few options:
- Change your INSERT statement so it inserts a value that is not NULL
- Change your table definition so that it allows for NULL values.
Let’s take a look at these solutions in more detail.
Solution 1: Adjust your INSERT Statement
To avoid the ORA-01400 error, you can adjust your INSERT statement to ensure that a non-NULL value is inserted.
Let’s see an example of this.
We have a customer table here:
CREATE TABLE customer (
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(100) NOT NULL,
email_address VARCHAR2(400)
);
The customer_id has been set as the primary key, which means it cannot take NULL values.
The customer_name field has the words NOT NULL after it. This means a NOT NULL constraint has been applied and NULL values are not allowed in this column.
The email_address column is allowed NULL values.
Now, let’s try insert a value.
INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (1, 'John', '[email protected]');
This value is inserted successfully.
Now, let’s try specify a NULL value for the customer_name:
INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (2, NULL, '[email protected]');
SQL Error: ORA-01400: cannot insert NULL into ("INTRO_USER"."TEST_CUSTOMER"."CUSTOMER_NAME") 01400. 00000 - "cannot insert NULL into (%s)" *Cause: An attempt was made to insert NULL into previously listed objects. *Action: These objects cannot accept NULL values.
The error appears because a NULL value is specified for the customer_name column, which is not allowed to have NULL values.
This could also happen with this statement. We have not specified the customer_name field in the INSERT statement columns, so a NULL value is used.
INSERT INTO customer (customer_id, email_address)
VALUES (3, '[email protected]');
SQL Error: ORA-01400: cannot insert NULL into ("INTRO_USER"."TEST_CUSTOMER"."CUSTOMER_NAME") 01400. 00000 - "cannot insert NULL into (%s)" *Cause: An attempt was made to insert NULL into previously listed objects. *Action: These objects cannot accept NULL values.
To resolve this, we need to ensure that a value of NULL is not added into this column.
Change the value of customer_name to something that is not NULL:
INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (3, 'Sarah', '[email protected]');
Or, if you’re getting data from another source, surround your value with the NVL function, which translates a NULL value to something else.
INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (3, NVL(input_name, 'Unknown'), '[email protected]');
If your values are coming from another system or source and definitely should not be NULL, then you might need to investigate the source of the data to find out why the values are NULL.
Solution 2: Change the Table Definition
Another way to resolve the ORA-01400 error is to change the definition of the table so that it allows for NULL values.
You can do this using the ALTER TABLE statement.
For example, to remove the NOT NULL constraint from the customer_name field in the customer table (mentioned above), you can run this command:
ALTER TABLE customer MODIFY COLUMN customer_name VARCHAR2(100);
This will remove the NOT NULL constraint from the table.
Now, you can insert a NULL value into this column.
INSERT INTO customer (customer_id, customer_name, email_address)
VALUES (4, NULL, '[email protected]');
Conclusion
So, that’s how you can resolve the ORA-01400 error.
It’s caused by inserting a NULL value into a column that cannot be NULL.
You can resolve it by adjusting your INSERT statement or modifying your table.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
I’m trying to insert a record into a table, but getting the error —
‘ORA-01400: cannot insert NULL into (….’. The table structure is:
I migrate from Mysql to Oracle.
On Mysql this works, but on Oracle it is not working. How can I fix this? Do I need write all column insert query or unselect not null option
asked Mar 6, 2013 at 12:25
9
Try this:
create or replace trigger EDITIONS_COR
before insert or update on EDITIONS
for each row
begin
if INSERTING then
select EDITIONS_SEQ.nextval into :new.ID from DUAL;
end if;
:new.CORDATE:=SYSDATE;
:new.USERNAME:=USER;
end;
ChrisF♦
134k31 gold badges254 silver badges325 bronze badges
answered Apr 17, 2014 at 17:07
ORA-01400
ORA-01400: обязательная (NOT NULL) колонка пропущена, или произошел NULL во время вставки
Причина:
Когда вы вставляете или изменяете строки, вы не указываете значение для колонки определенной как NOT NULL.
Действие:
Укажите значение для каждой колонки NOT NULL, или измените определение таблицы разрешающее нулевые значения в колонках, которые сейчас определены как NOT NULL.
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, GoldenGate, 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.
- Put the Column on the List of INSERT
- Remove NOT NULL constraint from the Column
- Provide a default Value for the Column
Put the Column on the List of INSERT
You have to modify the statement and provide a proper value to it at run-time.
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.
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, OK, 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.
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.
The error «ora-01400: cannot insert null into» occurs when an INSERT statement attempts to insert a NULL value into a column that does not allow NULL values. The solution to this error is to either modify the column to allow NULL values or provide a non-NULL value for the column in the INSERT statement.
Here’s the general syntax to modify a column to allow NULL values in Oracle:
ALTER TABLE table_name MODIFY column_name NULL;
And here’s an example of an INSERT statement that provides a non-NULL value for a column that does not allow NULL values:
INSERT INTO table_name (column1, column2, column3)
VALUES ('value1', 'value2', 'value3');
If you want to avoid the error in future insert statements, you can also add a NOT NULL constraint to the column. This constraint ensures that the column cannot contain NULL values:
ALTER TABLE table_name
MODIFY column_name data_type NOT NULL;
In summary, the «ora-01400: cannot insert null into» error can be resolved by either allowing NULL values in the column, providing a non-NULL value in the INSERT statement, or adding a NOT NULL constraint to the column.
ORA-01400: cannot insert null into (string) Solution
Have you gotten the “ORA-01400: cannot insert null into (string)” error? Learn what causes this and how to resolve it in this article.
Database StarBen
Frequently Asked Questions About The Error
What does the «ora-01400: cannot insert null into» error mean?
The error means that an INSERT statement is attempting to insert a NULL value into a column that does not allow NULL values.
Why is the error occurring?
The error is occurring because the database design specifies that the column cannot contain NULL values, but the INSERT statement is trying to insert a NULL value into that column.
How can I resolve the error?
You can resolve the error by either modifying the column to allow NULL values, providing a non-NULL value in the INSERT statement, or adding a NOT NULL constraint to the column.
Can I ignore the error and continue with the INSERT statement?
No, the error will prevent the INSERT statement from executing. You must resolve the error before the INSERT statement can be executed successfully.
Can the «ora-01400: cannot insert null into» error occur with UPDATE statements?
Yes, the same error can occur with UPDATE statements if an UPDATE statement attempts to update a column with a NULL value and the column does not allow NULL values.
Is it possible to insert NULL values into all columns?
No, it is not possible to insert NULL values into all columns. Each column may have different constraints and requirements, and some columns may not allow NULL values.