由于截断/删除而导致 SQL Server 主键违规？

发布于 2024-12-29 19:56:19 字数 697 浏览 4 评论 0原文

我有一组临时表，在插入到目标表之前我会在其中接受数据、清理和清理它们。目标表有主键约束，我插入的是主键。

在插入之前，我检查目标表中是否缺少主键。根据主键的缺失，我仅插入目标表中不存在的记录：

INSERT INTO Target
    SELECT 
        primKey
        , user_state
        , test_state
    FROM
        myStagingTable3

此时，stagingTable3 使用以下 where 子句仅包含目标表中不存在的数据：

WHERE
        primKey not in (Select primKey from Target)

不知何故，我得到了一个主键关键违规错误：

消息 2627，级别 14，状态 1。（过程在第 # 行失败...）违反主键约束“pk1101AE”。无法插入对象“目标”中的重复键

我的问题：

在什么情况下，当我要插入的键不存在于目标表中？
先前删除记录是否会导致主键被保留？如果是这样，我可以以某种方式解决这个问题吗？
还有什么吗？

很明显，我的临时表有密钥，而目标表没有。然而插入失败。

原文

I've got a set of staging tables where I accept data, scrub, and cleanse them before inserting to the target table. The target table has a primary key constraint and what I'm inserting is a primary key.

I check for the absence of the primary key in the target table before I insert. I insert only records that ARE NOT in the target table, based on the primary key's absence:

INSERT INTO Target
    SELECT 
        primKey
        , user_state
        , test_state
    FROM
        myStagingTable3

By this point, the stagingTable3 has only data that are NOT present in the Target Table using the following where clause:

WHERE
        primKey not in (Select primKey from Target)

Somehow, I'm getting a primary key violation error :

Msg 2627, Level 14, State 1. (procedure failed at line #...)
Violation of Primary Key constraint 'pk1101AE.' Cannot insert
duplicate key in object 'Target'

My questions:

under what conditions could a primary key violation occur when the
key that I want to insert is NOT present in the target table?
could a prior delete of records cause the primary key to be retained? If so,
can I work around this somehow?
something else?

It's pretty clear that my staging tables have the key and that the Target table does not. Yet the insert fails.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

相思碎 2025-01-05 19:56:19

发生这种情况是因为您的临时表包含具有相同 primKey 值的多行。

如果重复的 {user_state, test_state} 对放入插入中对您来说并不重要，则可以通过添加简单的 group by 来完全绕过该问题，像这样：

INSERT INTO Target
    (SELECT 
        primKey
    ,   MAX(user_state)
    ,   MAX(test_state)
    FROM
        myStagingTable3
    GROUP BY primKey)

This happens because your staging table contains multiple rows with the same value of primKey.

If it does not matter to you which {user_state, test_state} pair among the duplicated ones makes it into the insert, you can bypass the issue entirely by adding a simple group by, like this:

INSERT INTO Target
    (SELECT 
        primKey
    ,   MAX(user_state)
    ,   MAX(test_state)
    FROM
        myStagingTable3
    GROUP BY primKey)

回复收藏 0 原文

~没有更多了~