topic Re: Using Unique index instead of PK in Qlik Replicate

Using Unique index instead of PK

sergsyb — Wed, 09 Nov 2022 08:20:29 GMT

Hi guru,
The Source is MS SQL Server. We are trying to replicate table which doesn’t have PK, but it has an Unique index.
QR recognize Unique index and in table setting windows we can see

Record identifier -- Unique index
Identifier key -- index nameClusteredIndex-20221109-085004

But nonetheless QR print following message in errorlog - MS-REPLICATION is not enabled for table 'dbo.nopk'. Therefore, UPDATE changes to it will not be captured.

Documentation states following -MS-REPLICATION requires each of the source tables to have a primary key. But what about Unique index, which in the same way explicitly identifies a row in a table?
Why QR can’t use an unique index instead of Primary Key?

Re: Using Unique index instead of PK

KellyHobson — Wed, 09 Nov 2022 11:49:30 GMT

Hey @sergsyb ,

There is no way to avoid this warning message without setting up a PK on the table or enabling MS-CDC/ MS -Replicate.

This is a documented limitation for the SQL Server as a source endpoint.

Because you do not have a PK set up on this table, the following limitation applies (and is the cause for warning message):

If your database is not set up for MS-REPLICATION or MS-CDC, you can still capture tables that do not have a Primary Key, but bear in mind that in such a setup only INSERT/DELETE DML events will be captured. UPDATE events will be ignored.

It is also important to note that a DELETE statement executed on an UPDATED source record, will not be applied on the target.

https://help.qlik.com/en-US/replicate/November2020/Content/Replicate/Main/SQL%20Server/limitations_source_SQLServerDB.htm

https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/SQL%20Server/Replicate_Tables_that_do_not_have_a_Primary_Key.htm

Thus your options are:

1. Set up MS-Replicate or MS-CDC on this table

2. Define a PK on the table

3. Accept the limitation of no UPDATE and ignore the warning

Thank you,

Kelly

Re: Using Unique index instead of PK

SwathiPulagam — Wed, 09 Nov 2022 13:14:59 GMT

Hi @sergsyb ,

Adding a few more points to @KellyHobson

1) PK is mandatory for MS-replication Transactional publication and that is the reason you can't use non-pk tables with an MS-replication setup. Define a PK on the table or Accept the limitation of no UPDATE and ignore the warning if you want to go with MS replication setup.

2) for non-pk tables go with the MS-CDC setup.

https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/SQL%20Server/Replicate_Tab...

Thanks,

Swathi

Re: Using Unique index instead of PK

sergsyb — Thu, 10 Nov 2022 07:33:45 GMT

Thanks Kelly , Swathi

It's a pity that Qlik Replicate couldn’t use an unique index instead of PK.
May be it make sence to create feature request to support using an unique index instead of PK? Or may be such feature request already exists?

Re: Using Unique index instead of PK

Dana_Baldwin — Thu, 10 Nov 2022 16:12:34 GMT

Hi @sergsyb

If you would like to submit a feature request, you can do so here: https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Thanks,

Dana