// SQL Server 錯誤參考

SQL Server 錯誤 2627:違反 PRIMARY KEY 或 UNIQUE KEY

INSERT 或 UPDATE 會在 PRIMARY KEY 或 UNIQUE 約束中產生重複值。訊息會指出約束、資料表與重複的鍵值。

錯誤號碼
2627
嚴重性
14
類別
條件約束與索引鍵
錯誤訊息
Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls.

它的意義

主鍵和唯一性條件約束保證沒有兩個資料列共用相同的索引鍵值。當寫入操作會造成重複時,SQL Server 會以 2627 停止該陳述式,隨後是 3621(「陳述式已經終止」)。訊息會指出是哪種類型的條件約束(PRIMARY KEY 或 UNIQUE KEY)、其名稱、所在的資料表,以及自 SQL Server 2017 起還會顯示重複值本身。

錯誤 2601 是在非條件約束的唯一索引上出現的相同失敗。

常見原因

  • 插入已存在鍵的列,例如重新執行載入。
  • 在識別欄位中插入明確值,而其設定為 IDENTITY_INSERT 開啟時,或識別種子被重設為低於現有值時。
  • 來源本身就含重複索引鍵的批次插入。
  • 兩個工作階段同時插入同一個鍵:先檢查後插入的模式(IF NOT EXISTS ... INSERT)在沒有鎖定提示的情況下不安全。

如何修正

  1. 用訊息中顯示的值找出現有的資料列。

    SELECT * FROM dbo.Customers WHERE Email = N'[email protected]';
  2. 載入時略過已存在的資料列。

    INSERT INTO dbo.Customers (Email, Name)
    SELECT s.Email, s.Name
    FROM staging.Customers AS s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Customers AS c WHERE c.Email = s.Email);
  3. 在插入之前,先檢查來源中是否有重複。

    SELECT Email, COUNT(*) FROM staging.Customers GROUP BY Email HAVING COUNT(*) > 1;
  4. 對於並行環境下安全的 upsert,先更新,若什麼都沒更新到再插入,並全程持有範圍鎖。

    BEGIN TRANSACTION;
    UPDATE dbo.Customers WITH (UPDLOCK, SERIALIZABLE) SET Name = @Name WHERE Email = @Email;
    IF @@ROWCOUNT = 0
        INSERT INTO dbo.Customers (Email, Name) VALUES (@Email, @Name);
    COMMIT;
  5. 如果識別欄位發生衝突,請檢查並重新設定種子值。

    DBCC CHECKIDENT ('dbo.Customers', NORESEED);  -- shows current and max values
    DBCC CHECKIDENT ('dbo.Customers', RESEED);

安全地重現問題

CREATE TABLE #k (id int PRIMARY KEY);
INSERT INTO #k VALUES (1);
INSERT INTO #k VALUES (1);  -- Msg 2627 ... The duplicate key value is (1).
DROP TABLE #k;

問題

錯誤 2627 和 2601 有什麼區別?

2627 由 PRIMARY KEY 或 UNIQUE 條件約束擲出;2601 則由以 CREATE UNIQUE INDEX 建立的唯一索引擲出。原因與解法相同。

我可以讓 SQL Server 忽略重複的鍵嗎?

以 WITH (IGNORE_DUP_KEY = ON) 建立的唯一索引,會在 INSERT 時丟棄重複的資料列並發出警告,而不是讓陳述式失敗。請審慎使用,因為它會默默丟掉資料。

相關錯誤

更多條件約束與索引鍵錯誤

程式碼和訊息文字來自 Microsoft 資料庫引擎事件和錯誤參考。瀏覽完整的 SQL Server 錯誤清單 或 搜尋每個資料庫錯誤.

// 錯誤更少,修復更快

在 SQLly 裡操作 SQL Server

適用於 macOS、Windows 和 Linux 的原生 SQL 客戶端,具備結構描述感知的 IntelliSense、高風險陳述式防護與清楚的錯誤訊息,適用於 SQL Server 和你使用的其他資料庫。