Azure Data Sync Error - The INSERT statement conflicted with the FOREIGN KEY constraint

Question

Azure Data Sync Error - The INSERT statement conflicted with the FOREIGN KEY constraint

Aparna Manoharan 40

We are using Azure Data Sync to synchronise two databases . We are facing issue -Azure Data Sync Error - The INSERT statement conflicted with the FOREIGN KEY constraint

Sync is trying to insert values in the child table before inserting them into the Parent table

Is there a way to specify the order in which sync should get executed?

Vinodh247 34,661 Reputation points MVP Volunteer Moderator

2024-09-18T10:10:40.6366667+00:00

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-18T14:05:16.23+00:00

Hi @Aparna Manoharan,

Just checking in to see if the below answer provided by @Vinodh247 helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

Vinodh247 34,661 Reputation points MVP Volunteer Moderator

2024-09-18T10:10:40.6366667+00:00

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-18T14:05:16.23+00:00

Hi @Aparna Manoharan,

Just checking in to see if the below answer provided by @Vinodh247 helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Vinodh247 34,661 MVP Volunteer Moderator

Hi Aparna Manoharan,

Thanks for reaching out to Microsoft Q&A.

Azure data sync does not provide a direct option to specify the order in which tables are synchronized. But there are a few potential solutions to address this issue where foreign key constraints cause sync conflicts...

Disable foreignkey constraints temporarily during sync:

One approach is to temporarily disable foreign key constraints before the sync and re-enable them after the sync is complete. This can be done using triggers or stored procedures.

create a trigger or stored procedure that disables foreign key constraints before the sync process.
After the sync, enable the constraints again.

This will prevent conflicts during the sync, but be careful to ensure data integrity before enabling the constraints again.

Sync groups and sync direction:

Ensure that the sync direction is correctly configured in the sync group. If the databases are set up with bidirectional synchronization, azure data sync might not handle parent child table dependencies in the correct order. Setting up unidirectional sync from parent to child (for certain tables) might help.

Steps:

in the azure portal, go to the sync group settings.

Ensure the sync direction is appropriately set for parent-child tables (e.g., Parent: hub to member, child: member to hub).

Presync order handling with scripts:

You could write custom pre-sync scripts that manually sync parent tables first before syncing the child tables. Azure data sync allows you to execute custom sql scripts at the beginning and end of a sync operation. Set up a pre-sync script to check for the existence of parent records and insert them before syncing child records. This will help avoid the conflict where the child record is inserted before the parent.

Sync parent table first:

If the sync group includes both the parent and child tables, azure data sync might be syncing the child table first by default. You can try removing the child table from the sync group temporarily and syncing only the parent table first. Once the parent records are in sync, add the child table back into the sync group.

Data integrity checks and manual intervention:

If there are any existing integrity issues with the data (ex, Orphaned child records), clean up the data to ensure it is in a valid state for synchronization. Azure data sync cannot resolve such conflicts automatically.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Aparna Manoharan 40 Reputation points

2024-09-16T10:46:25.4466667+00:00
Hi @Vinodh247 Thanks for these suggestions.

We are using unidirectional set up now and sync runs automatically for every 30 mins

To disable foreignkey constraints temporarily during sync , should we use <table_name>_dss_insert_trigger to disable foreign key?

If so, which would be correct trigger or sp to enable the foreign key?

How do we maintain the changes if the triggers/sp are recreated when sync-group gets recreated?

Can you please help or share an article on how to set up pre and post sync script to handle -Presync order handling with scripts ?
Vinodh247 34,661 Reputation points MVP Volunteer Moderator

2024-09-16T14:03:36.7633333+00:00
To disable foreignkey constraints temporarily during sync , should we use <table_name>_dss_insert_trigger to disable foreign key?

The _dss_insert_trigger is an internal trigger created and managed by azure data sync. Modifying these triggers can lead to unexpected behavior and may be overwritten by the sync service during synchronization or when the sync group is updated.

If so, which would be correct trigger or sp to enable the foreign key?

the best approach is to use custom stored procedures (SP) or scripts that are triggered pre- and post-sync. ALTER TABLE <child_table_name> NOCHECK CONSTRAINT <fk_constraint_name>; --> disabling fk constraint ALTER TABLE <child_table_name> CHECK CONSTRAINT <fk_constraint_name>; --> enabling

How do we maintain the changes if the triggers/sp are recreated when sync-group gets recreated?

Use deployment scripts or Continuous Integration/Continuous Deployment (CI/CD) pipelines to redeploy custom objects after any sync group changes. Post-Deployment Scripts: Incorporate the creation of custom stored procedures and triggers into your database deployment process.

Can you please help or share an article on how to set up pre and post sync script to handle -Presync order handling with scripts ?

Microsoft’s official documentation on Azure Data Sync
Aparna Manoharan 40 Reputation points

2024-09-16T14:11:30.3166667+00:00

@Vinodh247 , the documentation link provided does not exist.
Vinodh247 34,661 Reputation points MVP Volunteer Moderator

2024-09-17T05:17:18.86+00:00

Hi,

Can you please help or share an article on how to set up pre and post sync script to handle -Presync order handling with scripts ?

I hope your question has been answered in another thread. Let me know if you need any other assistance.

https://learn.microsoft.com/en-us/answers/questions/2071203/set-up-a-pre-sync-script
Sai Raghunadh M 4,640 Reputation points Microsoft External Staff Moderator

2024-09-18T17:24:07.6166667+00:00

Hi @Aparna Manoharan,

Just checking in to see if the above answer provided by @Vinodh247 helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Azure Data Sync Error - The INSERT statement conflicted with the FOREIGN KEY constraint

1 answer

Your answer