I'm getting a missing foreign key error when I begin transactional replication.

Steps taken:

  1. Created a pull publication (live server)
  2. Created a copy only full backup
  3. Restored backup on reporting server
  4. Created a pull subscription on from backup (live server)
  5. Created a pull subscription on subscriber (reporting server)

I'm getting errors soon after looking through replication monitor:

The INSERT statement conflicted with the FOREIGN KEY constraint.

The issue is it's trying to insert a record into a table for which there's no corresponding record (although the record does exist on the live server).

Unless I've missed a step I'm not sure why this is happening. I had suspected it may be a case of distribution records getting cleaned up too early, so I disabled the SQL Agent job, but it's still happening.

Any help would be greatly appreciated.

  • The flow pattern of the copying process is at fault here. For example, assume you have tables Customer and Customer_Addresses, with a foreign key from Customer_Addresses pointing at Customer. If the Replication tries to replicate the Customer_Addresses table first, you will get foreign key errors for any customers not yet replicated to the Customers table. But if Customers is replicated first, you will not get foreign key errors replicating these two tables. – Laughing Vergil Oct 31 at 20:19
  • This is why Microsoft recommends adding "Not For Replication" for foreign keys - replication doesn't care about what order it replicates data in. Your best bet would be to remove the FK causing the conflict. – Laughing Vergil Oct 31 at 22:10
  • My mistake. I had assumed they would be applied sequentially in order. I'll give that a go. Thanks! – user2200550 Oct 31 at 23:19

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.