FIX: Fkey NOT FOR REPLICATION Doesn't Work Correctly

• Microsoft SQL Server, versions 6.0 and 6.5

SYMPTOMS

If you set up a FOREIGN KEY constraint to reference a table that is populated by replication (that is, a subscribed table), a constraint violation may occur if an UPDATE is run against the published table. This will cause the distribution task to fail with the following error:

   Msg 547, %s statement conflicted with %s constraint '%.*s'. The conflict
   occurred in database '%.*s', table '%.*s'%s%.*s%s

WORKAROUND

Change your UPDATE statement to run as an "on-page delete/insert." For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q135871
   TITLE     : INF: Update Methods Used in SQL Server 6.0

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

This problem will occur if the UPDATE on the published table is processed as a "deferred" or "full delete/insert" UPDATE statement. For additional information about determining what type of UPDATE statement is being processed, please see the Microsoft Knowledge Base article listed above.

SQL Server Replication will generate all statements to be sent to subscribers based on what is recorded in the transaction log of the published database. In the case of a "deferred" or "full delete/insert" UPDATE statement, the transaction log contains a series of DELETE and INSERT statements combined into one logical transaction. The entries for these commands appear in the MSjob_commands table in the distribution database as a series of DELETE statements, followed by INSERT statements, depending on the number of rows affected by the UPDATE. For example, a deferred UPDATE that affects 100 rows will result in 200 rows in the MSjob_commands table for that job.

Please note that the Microsoft SQL Server Transact-SQL Reference guide (in the UPDATE statement section) states that an UPDATE can never be "in-place" against a published table. However, it is still possible for it to be an "on-page delete/insert," which is considered a "direct" update. For additional information, please see the Microsoft Knowledge Base article listed above.