Key column <column-name> (1) is missing from delete on table <schema.table-name>, table has no PK but has UI with a DESC column (Doc ID 2080963.1)

Last updated on APRIL 03, 2023

Applies to:

Goal

When replicating using GoldenGate, the Discard file shows messages similar to:

Key column column-name (1) is missing from delete on table schema.table-name
Missing 1 key columns in delete for table schema.table-name.
...
Aborting transaction on <path>/ET beginning at seqno 415 rba 69903534
  error at seqno 415 rba 69903534
Problem replicating schema.table-name to schema.table-name
Mapping problem with delete record (target format)...
*
COLUMN-0 = COL1
000000: 46 43 41 52 43 |COL1 |

COLUMN-2 = 4
000000: 34 |4 |

COLUMN-3 = 1
000000: 31 |1 |

*

What is specific about this situation, is that the table has no keys, but has a Unique Index.
(NOTE: The GoldenGate Discard file reference COLUMN-0 equates to the Oracle database column COLUMN-1, et cetera...)

CREATE UNIQUE INDEX "<SCHEMA>"."<INDEX-NAME>" ON "<SCHEMA>"."<TABLE-NAME>" ("<COLUMN-1>", "<COLUMN-2>" DESC, "<COLUMN-3>", "<COLUMN-4>");

To confirm the columns are indexed:

SQL> select COLUMN_NAME, DESCEND from DBA_IND_COLUMNS where INDEX_NAME='<INDEX-NAME>' and TABLE_OWNER='<SCHEMA>';

COLUMN_NAME    DESCEND
-------------  --------
<COLUMN-1>     ASC
SYS_NC00008$   DESC
<COLUMN-3>     ASC
<COLUMN-4>     ASC

Note that the Unique Index DESC'ending column has a system generated name, rather than the actual column name.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.