Server Manager - Update Database - Fatal Error - Local base fails to update - Duplicate key value violates unique constraint - pk_keysidkey

Problem Description

This page provides the solution to the following error which you may encounter during migration:

SQL Error: ERROR:  duplicate key value violates unique constraint “pk_keysidkey”.
SQL Error: CONTEXT:  SQL statement “insert into Keys ( IdKey, KeyNam, KeyLib, KeyTyp, KeySubTyp , KeyClass, KeyProp, ObjTyp, IdUsrDevPro, KeyDevDat) values (  $1 , ‘Default Folder’, ‘’,‘FLD’, -1,20060,6,5071, ‘???’, $2 )”
SQL Error: PL/pgSQL function “cwmm_installation” line 58 at SQL statement
SQL Error: ERROR:  current transaction is aborted, commands ignored until end of transaction block

Applicable in CAST Version

*Observed on RDBMS

Action Plan

Usually this error has been reported for the case if the CAST base is migrated from Oracle to CSS and then upgrading CAST version from one version to another.

This error come if there are any inconsistencies in the Fld and keys tables before migration.

So the solution is to

1. Identify the inconsistencies in FLD & KEYS table.

   1. Run the below query

select * from fld

      ```sql
5;"Default Folder";0;"Default Folder"
6;"Default Folder";0;"Default Folder"

  ```sql

select * from keys where (KeyClass = 20060 and KeyProp = 10)

      ```sql
2;"Default Folder";"";"FLD   ";-1;20060;10;5071;"???";"1970-01-01 00:00:00";"";0;""

  ```text

So here we can see there is a missing row with idkey = 2. i.e. in ideal scenario, we must find in FLD table one idfld matching with the idkey and keyprop value in the above example we can see that there is no matching idfld in FLD table for idkey value 2 in KEYS table. So there is inconsistency, which explain the error in question.

2. Accordingly update the FLD table to resolve the inconsistency & the hence the migration issue.
   1. Update idfld in FLD table accordingly with idkey in keys to get rid of this.

      For the above example the update query is mentioned below which need to be run on the failing CAST base:

      ```sql
update Fld
set IdFld = IdFld-3

Notes/Comments

Related Pages