SQL Server How to handle primary key violation when updating composite keys

tvokkenx 于 12个月前发布在其他

关注(0)|答案(2)|浏览(99)

I have the following table in SQL Server, which keeps track of the users in a group, or what groups does a user belong to. Many-Many relationship.

CREATE TABLE a_group_user(
    user_id INT FOREIGN KEY REFERENCES dbo.appuser(user_id),
    group_id INT FOREIGN KEY REFERENCES dbo.usergroup(group_id),
    PRIMARY KEY(user_id, group_id)
);

A user can belong to multiple groups. For example:

user_id | group_id
------------------
1       |   1
1       |   3
2       |   3

In my business logic, I want to be able to add one group to another group. For this, I wrote a simple query which adds group 3 users to group 1 (Edit: group 3 should be emptied in the process):

UPDATE dbo.a_group_user
SET group_id = 1
WHERE group_id = 3

However, this gives me a primary key constraint violation error as expected, since changing the group_id to 1 for user_id of 1 with an existing group_id of 3 will result in duplicate records.

The error:
Violation of PRIMARY KEY constraint 'PK__a_group___A4E94E55A054C364'. Cannot insert duplicate key in object 'dbo.a_group_user'. The duplicate key value is (1, 1).

Please help me out

sql-server

来源：https://stackoverflow.com/questions/77683502/how-to-handle-primary-key-violation-when-updating-composite-keys

2条答案

按热度按时间

ckx4rj1h1#

You have to delete the record from this table if user exists in both the groups and then update group_id 3 to 1 for all. You need to use multiple queries as follows:

DELETE U
FROM dbo.a_group_user U
WHERE U.group_id = 1
AND EXISTS (
    SELECT 1
    FROM dbo.a_group_user UU 
    WHERE UU.group_id = 3
    AND uu.user_id = u.user_id
);

UPDATE dbo.a_group_user SET
    group_id = 1
WHERE group_id = 3;

赞(0）回复(0）举报 12个月前

oxiaedzo2#

You need to delete the existing group memberships and then insert the new group memberships. Here's an example:

Step 1: Delete existing group memberships for the specified users and group

DELETE
FROM dbo.a_group_user
WHERE user_id IN (
    SELECT user_id
    FROM dbo.a_group_user 
    WHERE group_id = 3
)
AND group_id = 1;

Step 2: Insert new group memberships

INSERT INTO dbo.a_group_user (user_id, group_id)
SELECT user_id, 1
FROM dbo.a_group_user
WHERE group_id = 3;

赞(0）回复(0）举报 12个月前

我来回答

SQL Server How to handle primary key violation when updating composite keys

2条答案

相关问题

热门标签

最新问答