Page 1 of 1
Unique constraint violation
Posted: Thu Feb 07, 2008 3:17 am
by satheesh_color
Hi,
I have a problem in unique constraint violation.
I have a table Vendor which contain the following unique index
CREATE UNIQUE INDEX VENDOR_U01 ON
VENDOR(VENDOR_xref_ID, VER_START_DATE)
CREATE UNIQUE INDEX VENDOR_U02 ON
VENDOR(VENDOR_ID, SUPPLIER_ID, MARKET_CODE, VER_START_DATE)
The thing is i am updating the table and then inserting rows to maintain versions.
While updating i set the key as VENDOR_xref_ID, VER_START_DATE. It's working fine.
While inserrting rows, i tried without keycolumns and with key columns(VENDOR_ID, SUPPLIER_ID, MARKET_CODE, VER_START_DATE)
It is throwing an error ORA-00001: unique constraint (EDW.VENDOR_U01) violated.
Please help me to solve this issue.
Thanks.
Posted: Thu Feb 07, 2008 5:23 am
by asitagrawal
Sample SQLs would help to understand the case better
Posted: Thu Feb 07, 2008 7:44 am
by chulett
Why kind of help do you need? Stop sending multiple records with the same values in the unique indexes. Hashed files should be used to validate your processing and drive the inserts v. updates.
Re: Unique constraint violation
Posted: Wed Feb 13, 2008 12:03 am
by baglasumit21
satheesh_color wrote:Hi,
I have a problem in unique constraint violation.
I have a table Vendor which contain the following unique index
CREATE UNIQUE INDEX VENDOR_U01 ON
VENDOR(VENDOR_xref_ID, VER_START_DATE)
CREATE UNIQUE INDEX VENDOR_U02 ON
VENDOR(VENDOR_ID, SUPPLIER_ID, MARKET_CODE, VER_START_DATE)
The thing is i am updating the table and then inserting rows to maintain versions.
While updating i set the key as VENDOR_xref_ID, VER_START_DATE. It's working fine.
While inserrting rows, i tried without keycolumns and with key columns(VENDOR_ID, SUPPLIER_ID, MARKET_CODE, VER_START_DATE)
It is throwing an error ORA-00001: unique constraint (EDW.VENDOR_U01) violated.
Please help me to solve this issue.
Thanks.
It will give the error as for the rows with the unique combination for second index, there may exist some rows which are not unique as per first index. You can query your source table to find out these duplicate rows. You need to handle them by deleting them from source itself or by updating them while loading to target.
Re: Unique constraint violation
Posted: Wed Feb 13, 2008 12:13 am
by baglasumit21
satheesh_color wrote:Hi,
I have a problem in unique constraint violation.
I have a table Vendor which contain the following unique index
CREATE UNIQUE INDEX VENDOR_U01 ON
VENDOR(VENDOR_xref_ID, VER_START_DATE)
CREATE UNIQUE INDEX VENDOR_U02 ON
VENDOR(VENDOR_ID, SUPPLIER_ID, MARKET_CODE, VER_START_DATE)
The thing is i am updating the table and then inserting rows to maintain versions.
While updating i set the key as VENDOR_xref_ID, VER_START_DATE. It's working fine.
While inserrting rows, i tried without keycolumns and with key columns(VENDOR_ID, SUPPLIER_ID, MARKET_CODE, VER_START_DATE)
It is throwing an error ORA-00001: unique constraint (EDW.VENDOR_U01) violated.
Please help me to solve this issue.
Thanks.
It will give the error as for the rows with the unique combination for second index, there may exist some rows which are not unique as per first index. You can query your source table to find out these duplicate rows. You need to handle them by deleting them from source itself or by updating them while loading to target.