Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
sergsyb
Contributor III
Contributor III

Using Unique index instead of PK

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?

Labels (4)
1 Solution

Accepted Solutions
SwathiPulagam
Support
Support

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

View solution in original post

4 Replies
KellyHobson
Former Employee
Former Employee

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_s...

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


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 

SwathiPulagam
Support
Support

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

sergsyb
Contributor III
Contributor III
Author

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?

Dana_Baldwin
Support
Support

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