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
Last updated on JANUARY 12, 2017
Applies to:Oracle GoldenGate - Version 184.108.40.206.0 to 220.127.116.11.0 [Release 11.1.1 to 12.1]
Information in this document applies to any platform.
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 /u01/goldengate/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 = FCARC
000000: 46 43 41 52 43 |FCARC |
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms