MERGE语句违反CHECK约束，如何用CASE语句或其他方案解决？

原始表结构（含CHECK约束） #

CREATE TABLE TableA
(
    [ID] int IDENTITY(1,1) NOT NULL,
    [EntityID] int,
    Denial nVarchar(20),
    CONSTRAINT Chk_Denial CHECK (Denial IN ('Y', 'N'))
)

注：原代码中Table a存在语法错误，修正为TableA。

原始MERGE语句（存在语法/逻辑错误） #

MERGE INTO TableA WITH (HOLDLOCK) AS tgt
USING (SELECT DISTINCT 
           JSON_VALUE(DocumentJSON, '$.EntityID') AS EntityID,
           JSON_VALUE(DocumentJSON, '$.Denial') AS Denial
       FROM Table1 bd
       INNER JOIN table2 bf ON bf.FileUID = bd.FileUID
       WHERE bf.Type = 'Payment') AS src ON tgt.[ID] = src.[ID]  

WHEN MATCHED 
))  THEN 
        UPDATE SET tgt.ID = src.ID,
                   tgt.EntityID = src.EntityID,
                   tgt.Denial = src.Denial,
            
WHEN NOT MATCHED BY TARGET
    THEN INSERT (ID, EntityID, Denial)
         VALUES (src.ID, src.EntityID, src.Denial)
    
THEN DELETE

语句本身的问题： #

• ID是IDENTITY自增列，不允许手动更新或插入指定值（除非开启IDENTITY_INSERT）
• 存在多余的闭合括号))、末尾逗号，以及缺失WHEN NOT MATCHED BY SOURCE的DELETE触发条件
• 匹配条件tgt.[ID] = src.[ID]无效，因为源查询未返回ID字段

执行错误信息 #

Error Message Msg 547, Level 16, State 0, Procedure storproctest1, Line 40 [Batch Start Line 0]

The MERGE statement conflicted with the CHECK constraint "Chk_Column". The conflict occurred in the database "Test", table "Table1", and column 'Denial'. The statement has been terminated.

错误核心原因：源数据中Denial字段值为Yes/No，不符合目标表Chk_Denial约束要求的Y/N。

可行解决方案 #

方案1：在源查询中用CASE转换值（推荐） #

直接在MERGE的USING子查询里完成值的转换，同时修正MERGE语句的语法错误：

MERGE INTO TableA WITH (HOLDLOCK) AS tgt
USING (SELECT DISTINCT 
           JSON_VALUE(DocumentJSON, '$.EntityID') AS EntityID,
           -- 转换Yes/No为Y/N，可处理异常值
           CASE JSON_VALUE(DocumentJSON, '$.Denial')
               WHEN 'Yes' THEN 'Y'
               WHEN 'No' THEN 'N'
               -- 可选：遇到非预期值时，可返回NULL或抛出错误
               -- ELSE NULL
               -- ELSE THROW 50000, '无效的Denial值', 1
           END AS Denial
       FROM Table1 bd
       INNER JOIN table2 bf ON bf.FileUID = bd.FileUID
       WHERE bf.Type = 'Payment') AS src 
ON tgt.[EntityID] = src.[EntityID] -- 改用EntityID作为匹配条件

WHEN MATCHED THEN 
        UPDATE SET 
                   tgt.EntityID = src.EntityID,
                   tgt.Denial = src.Denial -- 移除ID的更新操作
            
WHEN NOT MATCHED BY TARGET
    THEN INSERT (EntityID, Denial) -- ID由IDENTITY自动生成，无需指定
         VALUES (src.EntityID, src.Denial)

WHEN NOT MATCHED BY SOURCE THEN -- 补充DELETE的触发条件
    DELETE;

方案2：调整目标表结构，用计算列满足约束 #

如果需要保留原始Yes/No值，可新增计算列承载转换后的值，并将CHECK约束绑定到计算列：

-- 添加持久化计算列
ALTER TABLE TableA 
ADD Denial_Code AS CASE Denial 
                     WHEN 'Yes' THEN 'Y' 
                     WHEN 'No' THEN 'N' 
                   END PERSISTED;

-- 替换原CHECK约束
ALTER TABLE TableA DROP CONSTRAINT Chk_Denial;
ALTER TABLE TableA ADD CONSTRAINT Chk_Denial_Code CHECK (Denial_Code IN ('Y', 'N'));

此方案无需修改MERGE语句，但需调整表结构。

方案3：预处理源数据 #

如果源数据允许修改，直接批量修正Table1中的JSON字段值：

UPDATE Table1
SET DocumentJSON = JSON_MODIFY(DocumentJSON, '$.Denial', 
                               CASE JSON_VALUE(DocumentJSON, '$.Denial')
                                   WHEN 'Yes' THEN 'Y'
                                   WHEN 'No' THEN 'N'
                               END)
WHERE JSON_VALUE(DocumentJSON, '$.Denial') IN ('Yes', 'No');

修正后再执行原MERGE语句（需先修正语句本身的语法错误）。

内容的提问来源于stack exchange，提问作者Shekar Nandi