disable-check-constraints-for-replication.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	helpviewer_keywords	monikerRange
Disable Check Constraints for Replication	Disable Check Constraints for Replication	WilliamDAssafMSFT	wiassaf	03/14/2017	sql	table-view-index	how-to	CHECK constraints, disabling constraints [SQL Server], disabling disabling constraints constraints [SQL Server], check	>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

Disable Check Constraints for Replication

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-pdw-fabricsqldb]

You can disable check constraints in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. You can also explicitly disable check constraints for replication, which can be useful if you are publishing data from a previous version of [!INCLUDEssNoVersion].

Note

If a table is published using replication, check constraints are automatically disabled for operations performed by replication agents. When a replication agent performs an insert, update, or delete at a Subscriber, the constraint is not checked; if a user performs an insert, update, or delete, the constraint is checked. The constraint is disabled for the replication agent because the constraint was already checked at the Publisher when the data was originally inserted, updated, or deleted. For more information, see Specify Schema Options.

Before You Begin

Security

Permissions

Requires ALTER permission on the table.

Using SQL Server Management Studio

To disable a check constraint for replication

1. In Object Explorer, expand the table with the check constraint you want to modify, and then expand the Constraints folder.

2. Right-click the check constraint you wish to modify and then click Modify.

3. In the Check Constraints dialog box, under Table Designer, select a value of No for Enforce For Replication.

4. Click Close.

Using Transact-SQL

To disable a check constraint for replication

1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. The example creates a table with an IDENTITY column and a CHECK constraint on the table. The example then drops the constraint and recreates it specifying the NOT FOR REPLICATION clause.

   USE AdventureWorks2022;  
   GO  
   CREATE TABLE dbo.doc_exd (column_a int IDENTITY (1,1)   
   CONSTRAINT exd_check CHECK (column_a > 1))   
   
   ALTER TABLE dbo.doc_exd   
   DROP CONSTRAINT exd_check;   
   GO  
   ALTER TABLE dbo.doc_exd    
   ADD CONSTRAINT exd_check CHECK NOT FOR REPLICATION (column_a > 1);  
   

For more information, see ALTER TABLE (Transact-SQL).

See Also

Specify Schema Options