SAP Knowledge Base Article - Public

3329931 - Error "Could not insert into table due to a unique constraint violation" when loading snapshot for remote table based on ABAP CDS View

Symptom

The data replication fails for a remote table based on ABAP CDS View:

Error: {"timestamp":"XXXX-XX-XX XX:XX:XX.XXXXXXX", "origin":"_sys_task", "message":"Could not insert into table due to a unique constraint violation.; Statement: ,INSERT INTO ...

Environment

SAP Datasphere

Resolution

SAP Datasphere does NOT accept duplicate data based on Keys which is being uploaded to remote table.
If CDS View doesn't guarantee unique data records, error "Unique constraint violation" occurs.
Adjust and use following SQL Statement to check if CDS View have multiples entries with same key combination:

SELECT "KEY1",
       "KEY2",
       "KEY3",
LIST ALL KEY FIELDS HERE COUNT(*) FROM ABAP_CDS_VIEW GROUP BY "KEY1", "KEY2", "KEY3",
        LIST ALL KEY FIELDS HERE
HAVING COUNT(*) > 1

To avoid this problem, remodel the CDS view making sure that it does not returning duplicate data based on the key definition.
After that, refresh metadata changes in SAP Datasphere and load the snapshot again.

Keywords

ABAP_CDS, $F, dwc, data warehouse cloud, replicating, persisting, persist, persistance , KBA , DS-DI-RT , Remote Tables , Problem

Product

SAP Datasphere all versions