I have a record type with following releation Request Master (one to one) Payment Request (One to One) Invoice Header (One to One) Invoice Payment Info but getting following error
Unable to write to or delete from the source due to a data integrity violation.Error Details: Violation of UNIQUE KEY constraint 'UQ_PAYMENT_INFO_HEADER_INVOICE_ID'. Cannot insert duplicate key in object 'dbo.INVOICE_PAYMENT_INFO'. The duplicate key value is (21769).
Not sure why its throwing duplicate key as the ID PK attribute is blank
Discussion posts and replies are publicly visible
Most probably unique constraint UQ_PAYMENT_INFO_HEADER_INVOICE_ID is on the invoice_id column, and a row with invoice_id = 21769 already exists in INVOICE_PAYMENT_INFO.For 1:1 relationships, Write Records inserts a new child when PK is blank; but your FK (invoice_id) duplicates an existing row. Fetch the existing child record first and pass its PK so Write Records performs an update instead of insert.
Appian is attempting to INSERT a new row into INVOICE_PAYMENT_INFO instead of UPDATE the existing one — because the PK of INVOICE_PAYMENT_INFO is null during the write operation.
The constraint UQ_PAYMENT_INFO_HEADER_INVOICE_ID is on INVOICE_ID — not the PK. So even though PK is blank, the duplicate is caught on INVOICE_ID = 21769 which already exists.