Qlik Replicate: Oracle to SQL Violation of PRIMARY KEY Constraint

[Xiaodi_Shi]

Oracle to SQL Violation of PRIMARY KEY constraint. During full load, table is suspended due to Primary Key Violation.

[TARGET_LOAD     ]E:  RetCode: SQL_SUCCESS  SqlState: 23000 NativeError: 2627 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Violation of PRIMARY KEY constraint 'SERVICE_SYS_C001817631'. Cannot insert duplicate key in object 'dbo.SERVICE'. The duplicate key value is (G2190)

00010944: 2025-06-16T10:35:15 [TASK_MANAGER    ]W:  Table 'dbo'.'SERVICE' (subtask 1 thread 1) is suspended. RetCode: SQL_SUCCESS  SqlState: 23000 NativeError: 2627 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Violation of PRIMARY KEY constraint 'SERVICE_SYS_C001817631'. Cannot insert duplicate key in object 'dbo.SERVICE'. The duplicate key value is (G2190).; Failed to commit 10000 rows to target 'dbo.SERVICE'  (replicationtask.c:3239)

Resolution

This could be a classic capitalization issue between Oracle source to SQL target

Example

CREATE TABLE adam.products (
product_sku VARCHAR2(50) NOT NULL,
product_name VARCHAR2(255) NOT NULL,
price NUMBER(10, 2),
CONSTRAINT products_pk PRIMARY KEY (product_sku)
);

Then insert 2 identical records except for their capitalizations:

• Insert the first row with a lowercase PK.

  INSERT INTO adam.products (product_sku, product_name, price)
  VALUES ('key-logi-mx-m', 'Logitech MX Keys Mechanical (lowercase sku)', 169.00);

• Insert the second row with an uppercase PK
• This is also a simple, standard insert. It will success.
  

  INSERT INTO adam.products (product_sku, product_name, price)
  VALUES ('KEY-LOGI-MX-M', 'Logitech MX Keys Mechanical (UPPERCASE SKU)', 169.00);

Now, since Oracle is case-sensitive and the default installation of SQL Server is case-insensitive, it is a duplicate in SQL.

RetCode: SQL_SUCCESS SqlState: 23000 NativeError: 2627 Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Violation of PRIMARY KEY constraint 'PRODUCTS_PRODUCTS_PK'. Cannot insert duplicate key in object 'ADAM.PRODUCTS'. The duplicate key value is (KEY-LOGI-MX-M). [1022510] (sqlserver_endpoint_imp.c:5764)

Cause

It is changeable, by ALTER table on target.

ALTER TABLE ADAM.Products
ALTER COLUMN product_sku VARCHAR(50) COLLATE Latin1_General_CS_AS NOT NULL;

For Replicate , you would have to:

1. ALTER table on SQL target.
2. Set task to truncate on full load.
3. Reload table.

Environment

Qlik Replicate