Getting FKey violations in Replication

I have parent and child tables both in publisher and subscriber and I have
identical foreign keys in publisher and subscriber. However, sometimes
replication will report foreign key violation for the data getting
replicated.
To troubleshoot, I dropped and recreated the foreign keys with ‘Not For
Replication’ option which made the error go away. I ran some SQLs to see
where the data was inconsistent in the reported parent/child tables but
didn’t find any thing. To be sure, I dropped and recreated the foreign keys
and no error was reported.
My question is, why did I get the error in replication when I had the FKeys
without “Not For Replication” option. I would prefer to have FKeys check for
data integrity for replicated data on the subscriber side as well.
-A
Where did you do your validation? On the Publisher or Subscriber or both?
Also check to see if you are replicating cascading updates and deletes -
this could trigger this error.
What you should do next time you get this error is to look at the conflict
in the conflict viewer and determine which row is causing the problem.
In general you want to maintain DRI on both sides. Using the NFR option on
constraints will have the effect of not enforcing the constraint when the
DML is caused by a replication process. Merge replication can replicate
child records before parent records and this can cause the error you are
seeing.
Another option is to increase the download and upload generations per batch
setting, and if you get the error, re-run the agent..
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:364E4E6B-A323-43CE-A728-394846D36C05@.microsoft.com...
>I have parent and child tables both in publisher and subscriber and I have
> identical foreign keys in publisher and subscriber. However, sometimes
> replication will report foreign key violation for the data getting
> replicated.
> To troubleshoot, I dropped and recreated the foreign keys with 'Not For
> Replication' option which made the error go away. I ran some SQLs to see
> where the data was inconsistent in the reported parent/child tables but
> didn't find any thing. To be sure, I dropped and recreated the foreign
> keys
> and no error was reported.
> My question is, why did I get the error in replication when I had the
> FKeys
> without "Not For Replication" option. I would prefer to have FKeys check
> for
> data integrity for replicated data on the subscriber side as well.
> -A
>
>