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

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

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

      Query on FLD

      select * from fld

      Query result

      5;"Default Folder";0;"Default Folder"
      6;"Default Folder";0;"Default Folder"

      Query on Keys

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

      Query result

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

      Query Result Interpretation

      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:
      Update Query

      update Fld
      set IdFld = IdFld-3