| ORA-01400: cannot insert NULL into (string). [message #510200] |
Thu, 02 June 2011 20:05  |
youngb912
Messages: 56
Registered: October 2007
Location: New York
|
Member |
|
|
Hello Everyone, I need your help as much as possible. Thanks in advance.
Oracle version 11.2.0
OS Linux
I have a table with no primary key constraints with some roles containing null value/duplicates. I then decided to alter the table to add composite primary key constraints on four columns (a, b, c, and d). I did this by using the same script that was used to create the original table but this time adding the not null constraints.
I then took and export of the original table. I now want to import the data to the newly created table but I am now getting the error: ORA-01400: cannot insert NULL into (string).
I will like to perform the import without NULL. Is there a parameter in impdp that I can use? I tried DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS but it didn't work.
Beside options using impdp is there a way to do an insert statement like this insert into table a (select * from table) excluding NULL;?
Basically, I need to load the data into the newly created table without NULL.
I didn't want to classify my post as impdp because both impdp and DML solutions are acceptable to me.
Thanks
|
|
|
|
|
|
| Re: ORA-01400: cannot insert NULL into (string). [message #510236 is a reply to message #510201] |
Fri, 03 June 2011 01:54  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Quote:I then decided to alter the table to add composite primary key constraints on four columns (a, b, c, and d). I did this by using the same script that was used to create the original table but this time adding the not null constraints.
If the above is true, well, primary key constraint is not created when these columns are indicated as NOT NULL. Applying the primary key constraint will do that implicitly:SQL> create table test
2 (id number,
3 name varchar2(20)
4 );
Table created.
SQL> alter table test add constraint pk_t primary key (id);
Table altered.
SQL> desc test
Name Null? Type
----------------------------------------------------- -------- ---------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
SQL> insert into test (id, name) values (1, 'Little');
1 row created.
SQL> insert into test (id, name) values (1, 'Foot');
insert into test (id, name) values (1, 'Foot')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_T) violated
SQL>
But, making a column NOT NULL won't make it a primary key:SQL> drop table test;
Table dropped.
SQL> create table test
2 (id number,
3 name varchar2(20)
4 );
Table created.
SQL> alter table test modify id not null;
Table altered.
SQL> insert into test (id, name) values (1, 'Little');
1 row created.
SQL> insert into test (id, name) values (1, 'Foot');
1 row created.
SQL> |
|
|
|
| Re: ORA-01400: cannot insert NULL into (string). [message #510300 is a reply to message #510201] |
Fri, 03 June 2011 09:12 |
youngb912
Messages: 56
Registered: October 2007
Location: New York
|
Member |
|
|
@Blackswain, thanks for the quick response. The solution worked. I will follow the guideline going forward.
@Littlefoot, thanks for the observation - I mispoke. I didn't alter the table to add not null. I created a new table with the needed primary keys. I couldn't alter the table because two of the column to be used for primary key had null. While trying to load the data unto the newly created table that I encunter the error.
Thanks.
My issue is resolved.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
