// PostgreSQL 錯誤參考

PostgreSQL 23503:違反外鍵條件約束

有一列參照了不存在的父鍵,或者你刪除或改動了子資料列仍在參照的父資料列。

SQLSTATE
23503
名稱
foreign_key_violation
類別
完整性約束違規
錯誤訊息
insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(42) is not present in table "customers".

它的意義

外鍵要求子欄位中的每個值都必須存在於被參照的(父)欄位中。PostgreSQL 在兩種情況下會引發 23503:當你插入或更新沒有對應父鍵的子資料列時(is not present in table),以及當你刪除或更新還有子資料列指向的父資料列時(is still referenced from table).

這 DETAIL 行會給出鍵值和另一個資料表,你就知道該看哪些資料列。

常見原因

  • 在父列之前插入子列,例如載入 orders 在 customers.
  • 子資料列中的錯字或過期的 id。
  • 刪除仍有子列的父列,而不 ON DELETE CASCADE 或 SET NULL 在外鍵上。
  • 在子資料列仍參照舊值的情況下更新父資料列的主鍵。
  • 兩筆交易:其中一筆為某個父項插入子項,而另一筆剛把這個父項刪掉了。

如何修正

  1. 找出缺少的父資料列或剩餘的子資料列。

    SELECT * FROM customers WHERE id = 42;
    SELECT * FROM orders WHERE customer_id = 42;
  2. 先插入父資料表,或在一個交易內載入兩個資料表並將約束設為延遲(必須宣告為 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
  3. 刪除父項之前,先刪除或重新指派它的子項;如果這本來就是你想要的行為,也可以更改外鍵的刪除規則。

    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;
  4. 在為現有資料新增外鍵之前,先找出孤立的資料列。

    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"

相關錯誤

更多完整性條件約束違規錯誤

程式碼和訊息文字來自 PostgreSQL 文件,附錄 A:PostgreSQL 錯誤碼。瀏覽完整的 PostgreSQL 錯誤清單 或 搜尋每個資料庫錯誤.

// 錯誤更少,修復更快

在 SQLly 裡操作 PostgreSQL

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