Unique constraint violation

[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.

[asitagrawal]

Sample SQLs would help to understand the case better

[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.

[baglasumit21]

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.

[baglasumit21]

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.