Question: When creating a foreign key constraint, you have the option ""Enforce relationship for replication"". Should it be checked or unchecked?
Generally it should be checked. It simply means that the constraint will be included in the schema files with the snapshot and that referential integrity will be enforced on the subscriber.
With Merge replication the subscribers will also be modifying the published data. Therefore you will always want to keep the foreign keys on both publisher and subscriber to make sure the users are modifying the data correctly.
In order to do so just mark those constraint as ""NOT FOR REPLICATION"". It will make sure the foreign key constraints will not be fired by any replication agent, for example the merge agent.
The ""NOT FOR REPLICATION"" option is available for transactional, merge and snapshot replication.
You can get more information in SQL 2000 Books Online. See ""Alter Tables"", and search for ""NOT FOR REPLICATION"", this option is available for identity columns, foreign keys, and triggers.
An example of what would happen if you did not use this option
In the pubs database, we have a foreign key constraint between the Publishers and Titles tables. Because of the constraint, we cannot insert a new row into the Titles table with a new pub_id without first inserting one row into the Publishers table for that new id. An insert without a matching key will give a foreign key violation error.
Now we put both Publishers and Titles tables in the same merge publication, and we are not using NOT FOR REPLICATION on either publisher server or subscriber server.
Then the users at publisher server do the following updates:
- Insert one row in Publishers table with a new pub_id.
- Insert 1000 rows in Titles table for this new pub_id.
This works fine. We don't break the Foreign Key constraint. (Yet!)
Then the merge agent runs and trying to deliver the above 101 inserts into the subscriber. Remember, we don't have ""NOT FOR REPLICATION"" on the foreign key constraint on the subscriber. If the inserts on the Titles table are delivered to the subscriber before the insert on the Publishers table, then we will have that foreign key violation error.
In this case, if we turn on the ""NOT FOR REPLICATION"" option, then the foreign key constraint will not check any changes made by the merge agent.
There is a KB article on this Q308266 http://support.microsoft.com/default.aspx?scid=kb;en-us;Q308266
Note: In transactional replication the order of transactions being replicated is maintained.
Tip for Microsoft
It would certainly be useful if the create publication wizard gave you a warning when the ""NOT FOR REPLICATION"" was not enabled. It does for missing rowguids or not using 'not for replication' with identities.
Question for Microsoft
What we don't understand is what would you would ever gain from not using 'NOT FOR REPLICATION' on a foreign key within a merge publication? All it will do is create problems.
Or in other words, is there any point in having foreign key constraints firing during a merge synchronisation, and if not then why offer the option?