Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
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.
For more information, see: - CREATE TABLE (Transact-SQL), ALTER TABLE (Transact-SQL), CREATE TRIGGER (Transact-SQL), and ALTER TRIGGER (Transact-SQL)
- How to: Disable Foreign Key Constraints for Replication (Visual Database Tools)
- How to: Disable Check Constraints for Replication (Visual Database Tools)
See Also
Concepts
Considerations for All Types of Replication
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
15 September 2007 |
|