代码之家 › 专栏 › 技术社区 › Kung Fu Ninja

违反复合主键约束

sql-server sql

Kung Fu Ninja · 技术社区 · 1 年前

我正在尝试对具有复合主键的表运行更新。

相关代码:

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 )

已存在一条记录 ProjectID = 101 和 ClientID = 1 .

违反主键约束“PK__GC_1234”。无法在对象“dbo.a_test1”中插入重复键。重复键值为(101,1)

如何添加 IF .. EXISTS 条件或加入以排除这些记录?

2 回复 | 直到 1 年前

Dale K 1 年前

只要检查一下 UPDATE 将创建一个已经存在的PK,如果是这样,则将其排除,例如。

DECLARE @NewClientID int = 1;

UPDATE at1
SET ClientID = @NewClientID 
FROM a_test1 at1
WHERE ClientID in (2, 3) 
AND NOT EXISTS (
    SELECT 1
    FROM a_test1 at2
    WHERE at2.ClientID = @NewClientID
    AND at2.ProjectID = at1.ProjectId
);

Kung Fu Ninja 1 年前

不确定这是否是最有效的,但似乎奏效了。

DECLARE @newClientID int = 1


UPDATE a_test1
SET ClientID = @newClientID 
WHERE ClientID in ( 2, 3 ) 
AND ProjectID NOT IN  ( SELECT q.projectID
                    FROM ( SELECT projectID, clientID
                           FROM a_test1 
                           WHERE clientID = @newClientID ) q 
                     INNER JOIN a_test1 a ON a.projectID = q.projectID 
                     AND a.clientID in ( 2, 3 ) )

推荐文章