Error during synchronization: Merge statement conflicted with the foreign key constraint

When running synchronization job, there will be error “Failed to import: C:\ProgramData\BlueCieloECM\ExplorerSync\.....dat” and followed by the details "The MERGE statement conflicted with the FOREIGN KEY constraint 'FK_DocumentReference_DocumentRevision1' The conflict occurred in database 'Repository', table 'dbo.DocumentRevision', column 'ID'." This error is usually caused by wrong data in the DocumentReferenceExt table.

Solution

To resolve the error:

1. Run the following SQL query:

   Copy

   select * from DocumentReferenceExt dre
   where NOT EXISTS (select 1 from ImportDocumentRevision idr where idr.ID = dre.RevisionID)

   This query will return how the records with invalid value for the RevisionID property.

2. Verify if these invalid RevisionIDs exist in the ImportDocumentRevision (or DocumentRevision) table.

3. If the RevisionIDs do not exist, run this SQL query on the repository database:

   Copy

   delete from DocumentReferenceExt
   where NOT EXISTS(select 1 from ImportDocumentRevision idr where idr.ID = DocumentReferenceExt.RevisionID)

4. Reset the synchronization and delete all the *dat files.

   You can also back up the *dat files if you prefer.

5. Run the synchronization job for the repository.