// 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 を 2 つの方向で発生させます。親のないキーを持つ子行を挿入または更新したとき(is not present in table)、あるいは 1 つ設定しますis still referenced from table).

その DETAIL 行には、キーの値と相手のテーブルが示されるので、どの行を確認すればよいか分かります。

よくある原因

  • 親行より先に子行を挿入する(たとえば次のロード時) orders の前に customers.
  • 子行の入力ミス、または古い ID。
  • 子行がまだ残っている親行を、次の指定なしで削除する ON DELETE CASCADE または SET NULL 外部キーに対して。
  • 子テーブルが古い値を参照している状態で、親の主キーを更新している。
  • 2 つのトランザクション: 一方が、もう一方が削除したばかりの親に対する子行を挿入します。

修正方法

  1. 欠けている親または残っている子を見つけます。

    SELECT * FROM customers WHERE id = 42;
    SELECT * FROM orders WHERE customer_id = 42;
  2. 親を先に挿入するか、制約を遅延させて両方のテーブルを 1 つのトランザクションでロードしてください(宣言されている必要があります 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 をはじめ、お使いの他のデータベースにも対応します。