If you have recently upgraded to Spira v5.x from an earlier version and get an error message saving artifacts that mentions 'XPKTST_ARTIFACT_CUSTOM_PROPERTY' then you may have some old artifact custom property records that are blocking the new item saving

Symptoms

When you try creating and saving a new artifact in Spira, you get the following message on the screen:

APPLICATION.Business.EntityConstraintViolationException - Database constraint violation occurred

and the following detailed message in the Event Log:

Database constraint violation occurred [APPLICATION.Business.EntityConstraintViolationException]
An error occurred while updating the entries. See the inner exception for details. [System.Data.UpdateException]
Violation of PRIMARY KEY constraint 'XPKTST_ARTIFACT_CUSTOM_PROPERTY'. A duplicate key can not be inserted into the dbo.TST_ARTIFACT_CUSTOM_PROPERTY object.
The statement has ended. [System.Data.SqlClient.SqlException]

Cause

When you upgrade the system, the installer will reset the counts of the primary keys back to the highest artifact in the system. If you have some orphaned Artifact Custom Field records, they may have higher counts that the matching artifacts.

So when you try and save the item, the matching Artifact ID value is already taken by the orphaned row, causing the error to be logged.

Solution

Running the following SQL command  will find the orphaned rows for requirements:

SELECT * FROM TST_ARTIFACT_CUSTOM_PROPERTY
WHERE ARTIFACT_ID NOT IN (SELECT REQUIREMENT_ID FROM TST_REQUIREMENT WHERE PROJECT_ID = 1)
AND PROJECT_ID = 1 AND ARTIFACT_TYPE_ID = 1

If you get any results back, running the following SQL command will remove the orphaned rows

DELETE FROM TST_ARTIFACT_CUSTOM_PROPERTY
WHERE ARTIFACT_ID NOT IN (SELECT REQUIREMENT_ID FROM TST_REQUIREMENT WHERE PROJECT_ID = 1)
AND PROJECT_ID = 1 AND ARTIFACT_TYPE_ID = 1

Notes:

  1. We have used PROJECT_ID = 1 in both queries, that should be changed to your Project ID
  2. For other artifacts (test cases, incidents, etc.) there is a similar query, just email support and we can provide it for you.

If you have a cloud hosted instance, you should instead log a support ticket with Inflectra, provide the error message, and our team will do this for you.

Other Examples

For test cases, here's the query to use:

DELETE FROM TST_ARTIFACT_CUSTOM_PROPERTY
WHERE ARTIFACT_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE WHERE PROJECT_ID = 1)
AND PROJECT_ID = 1 AND ARTIFACT_TYPE_ID = 2

 

Article Info
  • Last Updated: 10/9/2018
  • Article ID: KB392
  • Views: 57