Upgrade from v5.4 to v6.0 Blocked by Foreign Key Constraints

Symptoms

When you run the Spira setup application to upgrade your instance from v5.4 to v6.0, you may get errors related to foreign key constraints. This can be due to missing (or non-enforced) constraints leading to bad data that breaks the installer when it tries to recreate the foreign key constraints.

For example, we have seen situations that should not in theory exist such as test steps that link to a non-existent test case. If this happens, you will need to run some SQL commands to clean up your database before you retry the upgrade to v6.0.

Solution

Based on our experience upgrading all of our cloud/SaaS customers from v5.4 to v6.0, we have found that the following SQL commands will remove the majority of orphaned data records that could block the upgrade process:

DELETE FROM TST_TEST_RUN WHERE TEST_CASE_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE)
DELETE FROM TST_TEST_SET_TEST_CASE WHERE TEST_CASE_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE)
DELETE FROM TST_TEST_SET_TEST_CASE WHERE TEST_SET_ID NOT IN (SELECT TEST_SET_ID FROM TST_TEST_SET)
DELETE FROM TST_TEST_CASE_DISCUSSION WHERE ARTIFACT_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE)
DELETE FROM TST_TEST_SET_DISCUSSION WHERE ARTIFACT_ID NOT IN (SELECT TEST_SET_ID FROM TST_TEST_SET)
DELETE FROM TST_REQUIREMENT_DISCUSSION WHERE ARTIFACT_ID NOT IN (SELECT REQUIREMENT_ID FROM TST_REQUIREMENT)
DELETE FROM TST_TASK_DISCUSSION WHERE ARTIFACT_ID NOT IN (SELECT TASK_ID FROM TST_TASK)
DELETE FROM TST_TEST_STEP WHERE TEST_CASE_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE)
DELETE FROM TST_TEST_RUN WHERE TEST_SET_ID NOT IN (SELECT TEST_SET_ID FROM TST_TEST_SET)
DELETE FROM TST_TEST_RUNS_PENDING WHERE TEST_SET_ID NOT IN (SELECT TEST_SET_ID FROM TST_TEST_SET)
DELETE FROM TST_RELEASE_USER WHERE RELEASE_ID NOT IN (SELECT RELEASE_ID FROM TST_RELEASE)
DELETE FROM TST_REQUIREMENT_USER WHERE REQUIREMENT_ID NOT IN (SELECT REQUIREMENT_ID FROM TST_REQUIREMENT)
DELETE FROM TST_RELEASE_TEST_CASE WHERE RELEASE_ID NOT IN (SELECT RELEASE_ID FROM TST_RELEASE)
DELETE FROM TST_RELEASE_TEST_CASE WHERE TEST_CASE_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE)
DELETE FROM TST_WORKFLOW_TRANSITION WHERE WORKFLOW_ID NOT IN (SELECT WORKFLOW_ID FROM TST_WORKFLOW)
DELETE FROM TST_REQUIREMENT_TEST_CASE WHERE TEST_CASE_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE)
DELETE FROM TST_TEST_STEP WHERE LINKED_TEST_CASE_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE)
DELETE FROM TST_TEST_CASE_PARAMETER WHERE TEST_CASE_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE)
DELETE FROM TST_TEST_RUN_STEP WHERE TEST_CASE_ID NOT IN (SELECT TEST_CASE_ID FROM TST_TEST_CASE)