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?
unique constraint error
Moderators: chulett, rschirm, roy
Re: unique constraint error
Your Update statement is getting failed so Datastage is trying to insert the record. Check your update query.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?
If Update query fails, for any reason system would go for insert.
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
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.
Last edited by chulett on Fri Jan 16, 2009 9:03 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
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.
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.