解決 SQL Server 2008 中 OUTPUT 子句與 CHECK 約束衝突的問題

問題背景

SQL Server 2005 引入的 OUTPUT 子句大幅提升資料轉移便利性，但官方文件指出：OUTPUT 子句的目標資料表不能包含啟用狀態的 CHECK 約束。然而實測發現，部分帶有啟用 CHECK 約束的資料表仍可作為 OUTPUT 目標，經比對發現關鍵在於約束的建立/啟用方式。

實測示範

建立測試環境：

USE tempdb;
GO

CREATE TABLE dbo.tb_source (id int);
CREATE TABLE dbo.tb_target (
    id int,
    CONSTRAINT CHK__tb_target__id CHECK (id > 0)
);
GO

-- 直接測試 OUTPUT INTO 會報錯
DELETE dbo.tb_source
OUTPUT deleted.* INTO dbo.tb_target;
/* 錯誤訊息：
訊息 333，層級 16，狀態 1
OUTPUT INTO 子句的目標表 'dbo.tb_target' 不能具有任何啟用的檢查約束...
找到檢查約束 'CHK__tb_target__id'。
*/

關鍵解法：使用 NOCHECK 重新啟用約束

-- 先停用再啟用所有約束
ALTER TABLE dbo.tb_target NOCHECK CONSTRAINT ALL;
ALTER TABLE dbo.tb_target CHECK CONSTRAINT ALL;
GO

-- 再次測試 OUTPUT INTO，成功執行
DELETE dbo.tb_source
OUTPUT deleted.* INTO dbo.tb_target;
-- (0 行受影響) 表示成功

驗證約束仍正常運作

-- 直接 INSERT 違反約束會被阻擋
INSERT dbo.tb_target VALUES (-1);
/* 錯誤：INSERT 語句與 CHECK 約束 "CHK__tb_target__id" 衝突 */

-- OUTPUT 過程中若資料違反約束也會被阻擋
INSERT dbo.tb_source VALUES (-1);
DELETE dbo.tb_source OUTPUT deleted.* INTO dbo.tb_target;
/* 錯誤：DELETE 語句與 CHECK 約束 "CHK__tb_target__id" 衝突 */

核心結論

只要 CHECK 約束是透過 WITH NOCHECK 啟用的，即可作為 OUTPUT 子句的目標資料表（與微軟錯誤提示不符）。同樣適用於使用 WITH NOCHECK ADD CONSTRAINT 建立的約束。

補充說明

• 預設建立約束用 WITH CHECK，啟用約束用 WITH NOCHECK
• WITH NOCHECK 僅影響建立/啟用時是否檢查現有資料，不影響新增資料的驗證
• WITH NOCHECK 會導致分割檢視失效，使用時需留意