Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION

SQL Server 2008 R2            

In some cases, it is desirable for user activity in a replication topology to be treated differently from agent activity. For example, if a row is inserted by a user at the Publisher and that insert satisfies a check constraint on the table, it might not be required to enforce the same constraint when the row is inserted by a replication agent at the Subscriber. The NOT FOR REPLICATION option allows you to specify that the following database objects are treated differently when a replication agent performs an operation:

• Foreign key constraints

  The foreign key constraint is not enforced when a replication agent performs an insert, update, or delete operation.

• Check constraints

  The check constraint is not enforced when a replication agent performs an insert, update, or delete operation.

• Identity columns

  The identity column value is not incremented when a replication agent performs an insert operation.

• Triggers

  The trigger is not executed when a replication agent performs an insert, update, or delete operation.

When a table is published, schema options control how objects are created in the subscription database. The default schema options differ by publication. When options are set to specify that foreign key constraints and check constraints are created in the subscription database, the NOT FOR REPLICATION option is set. The NOT FOR REPLICATION option is also set when replicating identity columns in merge publications and transactional publications that support updatable subscriptions. For more information about replicating identity columns, see Replicating Identity Columns.

In most cases the default settings are appropriate, but they can be changed if an application requires different behavior. The main area to consider is triggers. For example, if you define an insert trigger with the NOT FOR REPLICATION option set, all user inserts fire the trigger, but inserts from replication agents do not. Consider a trigger that inserts data into a tracking table: when the user inserts the row originally, it is appropriate for the trigger to fire and enter a row into the tracking table, but the trigger should not fire when that data is replicated to the Subscriber, because it would result in an unnecessary row being inserted in the tracking table.

To specify the NOT FOR REPLICATION option

The NOT FOR REPLICATION option can be specified in the following ways:

• Using replication schema options. For more information, see How to: Specify Schema Options (SQL Server Management Studio) and How to: Specify Schema Options (Replication Transact-SQL Programming).

• Directly in Transact-SQL syntax or in Microsoft SQL Server Management Studio when:

  • Creating or modifying an object in the publication database.

  • Creating or modifying an object in the subscription database. Objects are typically created manually (rather than by replication) only if a subscription is initialized without a snapshot.

主要针对identity这种自增长的列，SQLserver 2008 主要决定改列的增长值是不是因为replication在源端往目标端插入式触发一次+1，如果设置了改参数的Indentity列就不会自动增长，而是仅仅将值复制过去。除非如下图中报错，在创建publication时将identity的 value设置成manual 或者auto（事实从实际脚本发现即使设置了这参数，也依然无法解决这个问题）

具体解决方法见参考微软一片实验的blog：

https://blogs.msdn.microsoft.com/apgcdsd/2012/04/24/not-for-replication/

其中提及：Not for Replication”属性也适用于发布方和订阅方的Check约束、外键约束和标识列-Identity Column上。比如，从发布方复制数据到订阅方时，设置订阅服务器上的Check约束为“Not for Replication”，来防止复制代理在复制数据时由于此约束而被限制。

实际工作中的一个初始化报错例子

检查正确环境的表结构（注意标黄部分）：

报错环境的表结构（注意黄色部分并未设置 Not For Replication）