// PostgreSQL 錯誤參考
PostgreSQL 23503:違反外鍵條件約束
有一列參照了不存在的父鍵,或者你刪除或改動了子資料列仍在參照的父資料列。
- SQLSTATE
23503- 名稱
foreign_key_violation- 類別
- 完整性約束違規
它的意義
外鍵要求子欄位中的每個值都必須存在於被參照的(父)欄位中。PostgreSQL 在兩種情況下會引發 23503:當你插入或更新沒有對應父鍵的子資料列時(is not present in table),以及當你刪除或更新還有子資料列指向的父資料列時(is still referenced from table).
這 DETAIL 行會給出鍵值和另一個資料表,你就知道該看哪些資料列。
常見原因
- 在父列之前插入子列,例如載入
orders在customers. - 子資料列中的錯字或過期的 id。
- 刪除仍有子列的父列,而不
ON DELETE CASCADE或SET NULL在外鍵上。 - 在子資料列仍參照舊值的情況下更新父資料列的主鍵。
- 兩筆交易:其中一筆為某個父項插入子項,而另一筆剛把這個父項刪掉了。
如何修正
-
找出缺少的父資料列或剩餘的子資料列。
SELECT * FROM customers WHERE id = 42; SELECT * FROM orders WHERE customer_id = 42;
-
先插入父資料表,或在一個交易內載入兩個資料表並將約束設為延遲(必須宣告為
DEFERRABLE).ALTER TABLE orders ALTER CONSTRAINT orders_customer_id_fkey DEFERRABLE INITIALLY IMMEDIATE; BEGIN; SET CONSTRAINTS orders_customer_id_fkey DEFERRED; INSERT INTO orders (id, customer_id) VALUES (1, 42); INSERT INTO customers (id, name) VALUES (42, 'Ada'); COMMIT; -- checked here
-
刪除父項之前,先刪除或重新指派它的子項;如果這本來就是你想要的行為,也可以更改外鍵的刪除規則。
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey, ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE; -
在為現有資料新增外鍵之前,先找出孤立的資料列。
SELECT o.* FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE o.customer_id IS NOT NULL AND c.id IS NULL;
安全地重現問題
CREATE TEMP TABLE p (id int PRIMARY KEY); CREATE TEMP TABLE c (p_id int REFERENCES p (id)); INSERT INTO c VALUES (1); -- ERROR: ... violates foreign key constraint "c_p_id_fkey"
相關錯誤
23505重複鍵違反唯一約束23502null 值違反 not-null 限制42830無效的外鍵2BP01仍有相依物件存在547SQL Server:陳述式與條件約束衝突1452MySQL:外鍵失敗:無法新增子資料列1451MySQL:外鍵失敗:無法刪除父列ORA-02291Oracle:違反完整性條件約束 - 找不到父代鍵ORA-02292Oracle:違反完整性條件約束 - 找到子記錄SQLITE_CONSTRAINT_FOREIGNKEYSQLite:FOREIGN KEY 約束失敗
更多完整性條件約束違規錯誤
程式碼和訊息文字來自 PostgreSQL 文件,附錄 A:PostgreSQL 錯誤碼。瀏覽完整的 PostgreSQL 錯誤清單 或 搜尋每個資料庫錯誤.
// 錯誤更少,修復更快
在 SQLly 裡操作 PostgreSQL
適用於 macOS、Windows 和 Linux 的原生 SQL 客戶端,具備結構描述感知的 IntelliSense、高風險陳述式防護與清楚的錯誤訊息,適用於 PostgreSQL 和你使用的其他資料庫。