Error: An error occurred before executing the process 'Constituent Duplicate Search Business Process' Violation of PRIMARY KEY constraint #TMPxx. Cannot insert duplicate key in object 'dbo#TMP_CONSTITUENTS'

When running the Full Constituent Duplicate Search Process you may encounter the constraint error "An error occurred before executing the process 'Constituent Duplicate Search Business Process' Violation of PRIMARY KEY constraint #TMPxx. Cannot insert duplicate key in object 'dbo#TMP_CONSTITUENTS'"
This error can be generated if a household has more than one primary member. The below script can be used to identify the duplicate primary members and their households.
 
Select C.Name,C.LOOKUPID, H.LOOKUPID as Householdlookup, CH.ISPRIMARYMEMBER from CONSTITUENTHOUSEHOLD CH
Join CONSTITUENT C on C.ID= CH.ID
join CONSTITUENT H on H.ID=CH.HOUSEHOLDID
where CH.HOUSEHOLDID IN
( Select householdID from CONSTITUENTHOUSEHOLD where isprimarymember='1'
 group by HOUSEHOLDID having count(*) >1)
 And CH.ISPRIMARYMEMBER='1'
 order by CH.HOUSEHOLDID
 

Steps to Duplicate

1. Navigate to Constituents > Duplicates.
2. Run the Full Constituent Duplicate Search Process.
3. Note the constraint error.
 

Environment

 Blackbaud CRM
 4.0
 4.0.170.41

Was this article helpful?