When deleting records that are set to be hard delete a database constraint error is displayed

Version 4

    Environment:

    Service Desk: All Versions


    Review Date:

    17.04.2015



    Problem:

    When deleting an item that is set with Deletion Type of ‘Hard Deletion’ a database constraint error is displayed

     

    The DELETE statement conflicted with the REFERENCE constraint “databaseconstraintname”. The conflict occurred in database “databasename”, table “tablename”, column ‘columnname’. The statement has been terminated.
    

     


    Cause:

    When deleting an item which is set to be hard deleted, any records linked to the item being deleted have to be cleared or linked to another item first. This is because the data is removed from the database.

    For example if a Workstation configuration item (which is set as hard delete) has been added to an Incident was deleted you would see the following error:

     

    error.jpg

     

    This could similarly apply to a reference list that has a deletion type of ‘hard delete’ in object designer or any other objects which are set to be hard deleted.

    If the item was of soft deletion type the error would not be displayed because the data is not removed from the database it is only marked as deleted in the database.

     

    Soft delete is used so an historic record can be kept without seeing all the old data in the system. It is explained in the following community article http://community.landesk.com/support/docs/DOC-3842.

     

    Solution:

    ** Please do this in test before applying in live after a full backup **

    The solution is to change the linked value to another item or clear the link. Then the item can be deleted. The error message will tell you which database field the linked value is in, it can help if you have knowledge of the database tables at this point. In query are report designer an option exists to ‘Include real database names’ in the Designer Terminology options which can help query and show the linked data. In the example above in the screenshot this would be Incident. To show only the linked results the criteria attribute would need to be set to the column from the error (configuration item on incident in this example). The value would need to be the item being deleted. See the screenshot below. In this case we were deleting a Configuration Item call Workstation1. So we need to find all the links to this on Incident.

     

    criteria.jpg

     

    Then a bulk action can be used to change or clear the linked values. If for example a location was being hard deleted then this could be changed to a different location then the old value hard deleted. Please note there could be other links to the item you are trying to delete.

     

    ** Please do this in test before applying in live after a full backup **

     

    There can be more complex examples if you are unsure please contact your Support Provider.

    I have added some examples below of where the values could be hard coded items:

     

    The DELETE statement conflicted with the REFERENCE constraint "fkey$cf_user_config_item$ci". The conflict occurred in database "databasename", table "dbo.cf_user_config_item". column 'cf_config_item_guid'.

     

    If this error is displayed the configuration item is linked to a user in Administration these links have to be before removed before the items can be deleted.

     

    The DELETE statement conflicted with the REFERENCE constraint “fkey$cf_view_item$ci”. The conflict occurred in database “databasename”, table “dbo.cf_view_item”, column ‘cf_conifg_item_guid’. The statement has been terminated..

     

    This error is displayed if the item is used in the CI Structure diagram.