Handling Referential Integrity

If you have tables that are related to each other with foreign key constraints, running forced replication might disrupt the referential integrity of Subscribers if the forced-replication process doesn't update tables in the right order. For example, if you run UPDATE commands for several tables in one batch and the first replication stored procedure you run handles DELETE operations, the command might remove parent records on the Subscriber, which would violate foreign key constraints and generate an error message that would stop the replication process. To avoid such events, I recommend using the NOT FOR REPLICATION option for each foreign key you create on tables that participate in replication. This option means that SQL Server won't check the foreign key constraint when replication inserts or deletes data on Subscribers. Listing A shows how to use the NOT FOR REPLICATION option. For information about how referential integrity works, see Kalen Delaney's July 2000 article "Referential Integrity in SQL Server," InstantDoc ID 8687.