PostgreSQL 23505:重複鍵值違反唯一條件約束
INSERT 或 UPDATE 會在受主鍵、唯一約束或唯一索引保護的欄位中,產生第二筆相同值的資料列。
- SQLSTATE
23505- 名稱
unique_violation- 類別
- 完整性約束違規
它的意義
PostgreSQL 強制執行每個主鍵, UNIQUE 約束與唯一索引會在資料列寫入的當下就檢查。如果新寫入或修改的資料列,其鍵值與既有資料列相同(或與另一個交易已寫入但尚未提交的資料列相同),該陳述式就會以 23505 失敗,且不會保留任何變更。
這 DETAIL 行會指出欄位和重複的值,訊息則會指出條件約束或索引,讓你確切知道違反了哪一條規則。
常見原因
- 插入索引鍵已存在的列,例如重新執行匯入或 seed 腳本。
- A
serial或識別序列落後於資料,通常發生在以明確 id 載入資料列之後。下一個預設 id 會撞上現有的資料列:經典的Key (id)=(1) already exists. - 兩個工作階段同時插入同一個鍵;第二個會等第一個,第一個一提交,第二個就立刻失敗。
- 一個
UPDATE把唯一欄位設成另一列已有的值。
如何修正
-
找出現有的資料列,名稱記載於
DETAIL逐列檢查,判斷新資料是重複資料還是不同的記錄。SELECT * FROM users WHERE email = '[email protected]';
-
如果寫入應該改為更新現有的列,請將其改為 upsert,並搭配
ON CONFLICT.INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; -
如果應該簡單地跳過重複項,請使用
ON CONFLICT DO NOTHING.INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada') ON CONFLICT (email) DO NOTHING; -
如果重複發生在某個
serial或識別欄位的 ID,將序列移到現有最大值之後。SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT COALESCE(MAX(id), 0) + 1 FROM users), false);
安全地重現問題
CREATE TEMP TABLE t (id int PRIMARY KEY); INSERT INTO t VALUES (1); INSERT INTO t VALUES (1); -- ERROR: duplicate key value violates unique constraint "t_pkey"
問題
我從沒設定過 id,為什麼插入還是因為 id 欄位重複鍵而失敗?
資料表的序列落後於資料,通常是因為插入資料列時指定了明確的 id,或是還原時未更新序列值。可以使用下列指令重設: setval 到再往後一格 MAX(id).
ON CONFLICT 是否對任何唯一索引有效?
它適用於唯一索引或約束的欄位,或搭配 ON CONFLICT ON CONSTRAINT name。它需要 PostgreSQL 9.5 或更新版本。
相關錯誤
23503外鍵條件約束違規23502null 值違反 not-null 限制25P02目前的交易已中止2627SQL Server:違反 PRIMARY KEY 或 UNIQUE KEY2601SQL Server:無法插入重複的索引鍵資料列1062MySQL:索引鍵值重複ORA-00001Oracle:違反唯一條件約束SQLITE_CONSTRAINT_UNIQUESQLite:UNIQUE 約束失敗
更多完整性條件約束違規錯誤
程式碼和訊息文字來自 PostgreSQL 文件,附錄 A:PostgreSQL 錯誤碼。瀏覽完整的 PostgreSQL 錯誤清單 或 搜尋每個資料庫錯誤.
在 SQLly 裡操作 PostgreSQL
適用於 macOS、Windows 和 Linux 的原生 SQL 客戶端,具備結構描述感知的 IntelliSense、高風險陳述式防護與清楚的錯誤訊息,適用於 PostgreSQL 和你使用的其他資料庫。