FIX: Fkey NOT FOR REPLICATION Doesn't Work Correctly

Last reviewed: April 8, 1997
Article ID: Q148819

The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 and 6.5
BUG #: 13700 (Windows NT: 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

The NOT FOR REPLICATION option should allow changes submitted by the Distribution task on columns referenced by a FOREIGN KEY constraint from another table that otherwise would violate the constraint.

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

If you cannot change your UPDATE statement to meet this criteria, you may not be able to create a FOREIGN KEY constraint in the subscribed database to reference a subscribed table.

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.


Additional query words: sql6 rep kbfix6.50.sp2
Keywords : kbbug6.00 kbbug6.50 kbfix6.50.sp2 kbprg SSrvRep
Version : 6.0 6.5
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.