Page 1 of 1
unique constraint error
Posted: Fri Jan 16, 2009 1:10 am
by dnat
Hi,
I have a server job and have the target table as an oracle stage. I am doing inset/update (insert new rows or upsert existing rows). But still i am getting this oracle error
ORA-00001: unique constraint (CVID_UAT.PK_CV_WE_PRE_INSPECT) violated
Ideally we should not get this in an upsert mode, isnt it?
Re: unique constraint error
Posted: Fri Jan 16, 2009 3:32 am
by swarnkar
dnat wrote:Hi,
I have a server job and have the target table as an oracle stage. I am doing inset/update (insert new rows or upsert existing rows). But still i am getting this oracle error
ORA-00001: unique constraint (CVID_UAT.PK_CV_WE_PRE_INSPECT) violated
Ideally we should not get this in an upsert mode, isnt it?
Your Update statement is getting failed so Datastage is trying to insert the record. Check your update query.
If Update query fails, for any reason system would go for insert.
Posted: Fri Jan 16, 2009 8:44 am
by chulett
Ideally you shouldn't ever get any errors in any mode.
Insert/Update means the insert is first attempted and if it fails an update is attempted. In order for the insert to fail there must be a unique index / primary key on the target that gets violated. However, any error reported from this operation is from the
second action not the first and the way you get a unique constraint violation
from an update is if you are updating keys bound into a unique index to a value that already exists.
Posted: Fri Jan 16, 2009 8:55 am
by Katie09
Yes...this error should be not be there for upsert mode..
however check the constraints on the table and the keys used for the updation...table might have more than one contraint which might be violated by the data which you are proessing..
hope this helps..
Posted: Fri Jan 16, 2009 10:49 am
by srinagesh
try update else insert
Posted: Fri Jan 16, 2009 12:07 pm
by kandyshandy
so many answers going on...
DNAT, Did you add the sub property "Insert Array Size" and set it to 1 in Oracle EE stage?
Posted: Fri Jan 16, 2009 12:15 pm
by chulett
Server job, so no "EE" stage here. And the OCI stage will automatically drop the array size down to 1 if it is not already there when using these combo actions.
Posted: Fri Jan 16, 2009 12:21 pm
by kandyshandy
Sorry Craig. I overlooked.
Posted: Wed Jan 21, 2009 4:36 am
by dnat
hi,
my update clause had two keys(in the where clause as well as keys indicated in the metadata) but the table had only one key. I think this has caused the updated to fail. I am yet to do the full testing, but since i had to complete the task in a hurry, i deleted the records which had to be insert/update and reran the job..all the records were inserted properly..
so, here as craig said the issue happens to be with the update statement.