停用複寫的檢查條件約束Disable Check Constraints for Replication

本主題適用於: 是SQL Server (從 2016 開始)是Azure SQL Database是Azure SQL 資料倉儲 是平行處理資料倉儲 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

您可以使用 SQL Server 2017SQL Server 2017Transact-SQLSQL Server Management Studio ,在 Transact-SQLTransact-SQL中停用檢查條件約束。You can disable check constraints in SQL Server 2017SQL Server 2017 by using Transact-SQLSQL Server Management Studio or Transact-SQLTransact-SQL. 您也可以明確停用複製的檢查條件約束,當您從舊版 SQL ServerSQL Server發行資料時,這會相當實用。You can also explicitly disable check constraints for replication, which can be useful if you are publishing data from a previous version of SQL ServerSQL Server.

注意

如果使用複寫發行資料表,則會自動停用複製代理程式所執行作業的檢查條件約束。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

需要資料表的 ALTER 權限。Requires ALTER permission on the table.

使用 SQL Server Management StudioUsing 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.

使用 Transact-SQLUsing Transact-SQL

若要停用複製的檢查條件約束To disable a check constraint for replication

  1. [物件總管] 中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute. 此範例會建立包含 IDENTITY 資料行及 CHECK 條件約束的資料表。The example creates a table with an IDENTITY column and a CHECK constraint on the table. 接著範例會卸除條件約束再重新建立,並指定 NOT FOR REPLICATION 子句。The example then drops the constraint and recreates it specifying the NOT FOR REPLICATION clause.

    USE AdventureWorks2012;  
    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);  
    

    如需詳細資訊,請參閱 ALTER TABLE (Transact-SQL)For more information, see ALTER TABLE (Transact-SQL).

另請參閱See Also

指定結構描述選項Specify Schema Options