CRM 4.0: DELETE statement conflicted with the REFERENCE constraint “FK__SystemUse__UserI__7755B73D"


While working in Microsoft Dynamics CRM 4.0, I found that SQL job “MSCRM_CONFIG.HardDelete” was failing with following error message:

The DELETE statement conflicted with the REFERENCE constraint "FK__SystemUse__UserI__7755B73D". The conflict occurred in database "MSCRM_CONFIG", table "dbo.SystemUserAuthentication", column 'UserId'. [SQLSTATE 23000] (Error 547)  The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

When I looked into the SQL job properties, I found that it was executing a SQL Stored Procedure exec p_hardDelete :

This SQL Stored Procedure is located under Programmability in the MSCRM_CONFIG database:

 

When I looked into this stored procedure, I found the following SQL script:

If you look into the order of the script you will find:

DELETE FROM SystemUser WHERE IsDeleted = 1 ---- Parent Table. Will error out due to FK constraint defined in below child table (SystemUserAuthentication)

DELETE FROM SystemUserAuthenticationProperties WHERE Id IN (SELECT Id FROM SystemUserAuthentication WHERE IsDeleted = 1)

DELETE FROM SystemUserAuthentication WHERE IsDeleted = 1

We can modify this script to make it error free by changing the order as follows:

DELETE FROM SystemUserAuthenticationProperties WHERE Id IN (SELECT Id FROM SystemUserAuthentication WHERE IsDeleted = 1)

DELETE FROM SystemUserAuthentication WHERE IsDeleted = 1

DELETE FROM SystemUser WHERE IsDeleted = 1

After changing the order of the SQL stored procedure exec p_hardDelete , the SQL job “MSCRM_CONFIG.HardDelete” will execute  without any error message.


Comments (0)

Skip to main content