AAI Upgrade Error: ORA-02291 Integrity Constraint Violations (XFKUSERPROFILEPREFERENCEVALUE) violated - parent key not found during Database Upgrade

Products

• Automation Analytics & Intelligence

Issue/Introduction

When performing an upgrade to Automation Analytics & Intelligence (AAI) version 24.2 or higher, the database upgrade script (e.g., upgradeJawsDbTo24.2.0.sql) fails with the following error:

SQL Error: ORA-02291: integrity constraint (XFKUSERPROFILEPREFERENCEVALUE) violated - parent key not found

This typically occurs during INSERT or UPDATE operations on tables such as PreferenceValue, where the script expects a hardcoded ID like userprofile0 to exist in the parent UserProfile table, but a different admin ID is present in the environment.

Environment

AAI 

Cause

The upgrade script contains hardcoded references to userprofile0. If the existing 'admin' user in the UserProfile table has a different userId (often seen in environments upgraded through multiple versions), the foreign key constraint is violated because the parent key userprofile0 cannot be found.

Resolution

To resolve this, you must temporarily align the admin user ID with the ID expected by the upgrade script. The following tables in version 24.4 are known to contain `userId` or `ownerId` references that may need updating:

- PreferenceValue
- UIView
- UIWidget
- Report
- JawsRoleUserProfile
- LoginHistory
- UserProfile
- UserPreference
- ReportShare_UserProfile
- ReportFavorite_UserProfile
- PreferenceShare_UserProfile

Workaround / Fix Steps

1. **Identify the current admin ID:**
   Review this command before running it.

   SQL

   select userId, userName from UserProfile where userName = 'admin';
   

2. **Stop the AAI application.**

 

3. Update the IDs:
   

Replace `USER_ID_FROM_SELECT_QUERY` in the following queries with the actual `userId` retrieved in Step 1.

   This command will make changes to your system. Review it carefully before running.

   SQL

   update PreferenceValue set ownerId = 'userprofile0' where ownerId = 'USER_ID_FROM_SELECT_QUERY';
   update UIView set ownerId = 'userprofile0' where ownerId = 'USER_ID_FROM_SELECT_QUERY';
   update UIWidget set ownerId = 'userprofile0' where ownerId = 'USER_ID_FROM_SELECT_QUERY';
   update Report set ownerId = 'userprofile0' where ownerId = 'USER_ID_FROM_SELECT_QUERY';
   update JawsRoleUserProfile set userId = 'userprofile0' where userId = 'USER_ID_FROM_SELECT_QUERY';
   update LoginHistory set userId = 'userprofile0' where userId = 'USER_ID_FROM_SELECT_QUERY';
   update UserProfile set userId = 'userprofile0' where userId = 'USER_ID_FROM_SELECT_QUERY';
   update UserPreference set userId = 'userprofile0' where userId = 'USER_ID_FROM_SELECT_QUERY';
   update ReportShare_UserProfile set userId = 'userprofile0' where userId = 'USER_ID_FROM_SELECT_QUERY';
   update ReportFavorite_UserProfile set userId = 'userprofile0' where userId = 'USER_ID_FROM_SELECT_QUERY';
   update PreferenceShare_UserProfile set userId = 'userprofile0' where userId = 'USER_ID_FROM_SELECT_QUERY';
   commit;
   

4. Resume the AAI database upgrade script.

 

5. Start the AAI application.