Foreign Key Constraint Violations - DML On Replicated Tables

  • 63Views
  • Last Post 07 March 2018
0
votes
API User Reporting posted this 01 March 2018

Greetings:

I would like to suggest that when you create foreign keys in the target database for replication that you do so such that the foreign key is disabled (NOCHECK in SQL Server).

This is because there is no guaranteed that replication will perform DML actions against replicated tables in such a way as to ensure compliance with enabled foreigh key constraints.

It is useful to have the foreign keys created even if they are disabled because the relationships established by such are useful artifacts in ER diagrams, etc.

Please advise.

Respectfully,

Darryll Petrancuri

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 02 March 2018

Thank you for your suggestion. We will consider it.

Best regards,

Mariia

0
votes
API User Reporting posted this 05 March 2018

Mariia:

Thank you for your reply.

I think you must do more than consider it. I think you must implement it, otherwise you are compromising the data integrity of SkyVia replicated objects due to foreign key constraint violations because you do not have a mechanism in place, nor can you, to guarantee the correct data replication order to avoid such.

Respectfully,

Darryll Petrancuri

0
votes
Mariia Zaharova posted this 06 March 2018

Thank you for your reply. We will definitely inform you about the results as soon any are available.

We appreciate your valuable contribution to Skyvia.

 

Best regards,

Mariia

0
votes
API User Reporting posted this 07 March 2018

Mariia:

I need to know whether or not you are going to do as I state needs to be done, which is create the foreign keys with NOCHECK and then immediately alter them to NOCHECK. Adding with NOCHECK simply bypasses referential integrity check at the time the constraint is created. That is why the constraint must be immediately altered to NOCHECK to make sure foreign key constraints are not enforce after the constraint is created.

If you do not do such, replication can, and will, have errors that get created because of foreign key constraint violations.

Not creating the constraints is not a viable alternative, as from a data modeling perspective alone, it is useful, if not necessary to have the foreign keys created in order to have viable metadata in the target database to understand the relationships between replicated objects.

So, please tell me if this is going to be done. It is a major source of concern for my client with respect to successful data replication without the loss of the critical metadata provided by a NOCHECK foreign key constraint.

Respectfully,

Darryll Petrancuri

P. S.

It also improves replication performance to not have the constraints being checked during replication.

 

0
votes
Mariia Zaharova posted this 07 March 2018

Your suggestion is quite useful and will improve Skyvia, so yes, we plan to implement it. As soon as any results regarding this functionality are available, we will inform you.

Best regards,

Mariia

Close