Site icon IT Tutorial

ORA-01400: cannot insert NULL into

I got ” ORA-01400: cannot insert NULL into ” error in Oracle.

 

ORA-01400: cannot insert NULL into

 

Details of error are as follows.


ORA-01400: cannot insert NULL into (string)

Cause: An attempt was made to insert a NULL into the column "USER"."TABLE"."COLUMN".

For example, if you enter:

connect scott/tiger create table a (a1 number not null); insert into a values (null);
Oracle returns:

ORA-01400 cannot insert NULL into ("SCOTT"."A"."A1") : which means you cannot insert NULL into "SCOTT"."A"."A1".

Action: Retry the operation with a value other than NULL

To see if a table allows NULL values, use tthe SQL*Plus desc command on the table name:


 

 

cannot insert NULL into

This ORA-01400 errors are related with the NULL value for the not null or primary key columns.

 

For example; I have created test table and id column has NOT NULL constraint as follows.

CREATE TABLE ora_1400_error
(
id NUMBER(12) NOT NULL,
name VARCHAR2(100)
);

 

When you insert any NULL value, you will get this error.

INSERT INTO ora_1400_error
(id,
name)
VALUES
(NULL,
'Ora-01400 error');

ORA-01400: cannot insert NULL into ID

 

You need to insert any not null value to this column as follows.


INSERT INTO ora_1400_error
(id,
name)
VALUES
(1400,
'Ora-01400 error');

 

Or you can drop or disable this NOT NULL Constraint to solve this error.

You can find the constraint of table as follows.

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 = 'ORA_1400_ERROR' and a.column_name = 'ID';

CONSTRAINT_NAME STATUS
------------------------- --------
SYS_C006334 ENABLED

 

You can disable the constraint as follows.

 

SQL> alter table ORA_1400_ERROR disable constraint SYS_C006334 ;

Table altered.

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

Exit mobile version