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.

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.

 

ORA-01400 Solution

To resolve the ORA-01400 error, you have a few options:

1. Change your INSERT statement so it inserts a value that is not NULL
2. 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.