Resolving FK Constraint Errors When Deleting Inactive Contacts
Sometimes, when deleting inactive contacts, you may receive a constraint error similar to these ones:
“[DataConnection.HandleError]: Query: Proc_OM_Contact_MassDelete Caused exception: The DELETE statement conflicted with the REFERENCE constraint "FK_OM_Search_OM_Activity". The conflict occurred in database "SomeKenticoDatabase", table "dbo.OM_Search", column 'SearchActivityID'. The DELETE statement conflicted with the REFERENCE constraint "FK_OM_Activity_OM_Contact_Active". The conflict occurred in database "SomeKenticoDatabase", table "dbo.OM_Activity", column 'ActivityActiveContactID'.”
“[DataConnection.HandleError]: Query: Proc_OM_Contact_MassDelete Caused exception: The DELETE statement conflicted with the REFERENCE constraint "FK_OM_PageVisit_OM_Activity". The conflict occurred in database "SomeKenticoDatabase ", table "dbo.OM_PageVisit", column 'PageVisitActivityID'. The DELETE statement conflicted with the REFERENCE constraint "FK_OM_Activity_OM_Contact_Active". The conflict occurred in database "SomeKenticoDatabase ", table "dbo.OM_Activity", column 'ActivityActiveContactID'.”
These issues are caused by incorrectly saved activity types in the OM_Search or OM_PageVisit tables. Only internalsearch or externalsearch activity types should be saved in the OM_Search table and only pagevisit or landingpage activity types in the OM_PageVisit table. If there are any other activity types saved in these tables (because of some data inconsistency), you will get an FK constraint error when attempting to delete inactive contacts.
The solution is to delete the incorrectly saved activity types from both tables. You can run the following SQL script against your database (it will delete all incorrectly saved activity types from both tables):
Please note: Even though the SQL script should work as expected and was tested, please make sure that you back up your database first!
USE <name of your database>
-- Deletes incorrect activity types saved in the OM_Search table
DELETE FROM OM_Search WHERE SearchID IN
(SELECT SearchID
FROM OM_Search S
JOIN OM_Activity A ON S.SearchActivityID = A.ActivityID
WHERE A.ActivityType != 'internalsearch' AND A.ActivityType != 'externalsearch')
-- Deletes incorrect activity types saved in the OM_PageVisit table
DELETE FROM OM_PageVisit WHERE PageVisitID IN
(SELECT PageVisitID
FROM OM_PageVisit P
JOIN OM_Activity A ON P.PageVisitActivityID = A.ActivityID
WHERE A.ActivityType != 'pagevisit' AND A.ActivityType != 'landingpage')
All you need to provide is the name of your database (the first line of the script) and the script will do the rest. After that, you should be able to continue in deleting inactive contacts.