// 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. 부모를 먼저 삽입하거나, 제약 조건을 연기(deferred) 상태로 한 트랜잭션에서 두 테이블을 모두 로드하세요(다음으로 선언되어 있어야 합니다 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 작업하기

스키마를 아는 IntelliSense, 위험한 문장에 대한 가드레일, 명확한 오류 메시지를 갖춘 macOS, Windows, Linux용 네이티브 SQL 클라이언트입니다. PostgreSQL은 물론 평소 쓰는 다른 데이터베이스까지 지원합니다.