复合主键约束冲突：如何用IF EXISTS或连接排除冲突记录

问题场景 #

现有包含复合主键(ProjectID, ClientID)的表a_test1，表结构、测试数据及更新语句如下：

DROP TABLE IF EXISTS a_test1

CREATE TABLE a_test1 
(
    ProjectID int NOT NULL,
    ClientID int NOT NULL,

    CONSTRAINT [PK__GC_1234] 
        PRIMARY KEY CLUSTERED (ProjectID ASC, ClientID ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO a_test1 VALUES (101, 1) 
INSERT INTO a_test1 VALUES (101, 2) 
INSERT INTO a_test1 VALUES (103, 2) 
INSERT INTO a_test1 VALUES (104, 1)
INSERT INTO a_test1 VALUES (104, 2)
INSERT INTO a_test1 VALUES (104, 3)

DECLARE @newClientID int = 1

UPDATE a_test1
SET ClientID = @newClientID 
WHERE ClientID in ( 2, 3 ) 

执行上述更新语句时，因表中已存在(101,1)的记录，更新(101,2)为(101,1)会触发主键约束冲突：

Violation of PRIMARY KEY constraint 'PK__GC_1234'. Cannot insert duplicate key in object 'dbo.a_test1'. The duplicate key value is (101, 1)

解决方案 #

可以通过两种方式排除会导致主键冲突的记录：

方法1：使用NOT EXISTS子查询 #

在更新条件中增加判断，跳过那些更新后会与现有主键重复的记录：

DECLARE @newClientID int = 1

UPDATE a_test1
SET ClientID = @newClientID 
WHERE ClientID in (2, 3)
AND NOT EXISTS (
    SELECT 1 
    FROM a_test1 t
    WHERE t.ProjectID = a_test1.ProjectID 
    AND t.ClientID = @newClientID
)

逻辑说明：子查询检查当前要更新的记录的ProjectID是否已存在ClientID = @newClientID的条目，若存在则不更新该条记录。

方法2：使用LEFT JOIN过滤冲突记录 #

通过左连接匹配冲突记录，只更新无冲突的条目：

DECLARE @newClientID int = 1

UPDATE t1
SET t1.ClientID = @newClientID 
FROM a_test1 t1
LEFT JOIN a_test1 t2 ON t1.ProjectID = t2.ProjectID AND t2.ClientID = @newClientID
WHERE t1.ClientID in (2, 3)
AND t2.ProjectID IS NULL

逻辑说明：左连接后，t2.ProjectID IS NULL的记录表示当前ProjectID下不存在ClientID = @newClientID的条目，仅对这些记录执行更新。

内容的提问来源于stack exchange，提问作者Kung Fu Ninja