외래 키 위반사항 찾기 및 수정하기

해당 기본 키가 없는 외래 키를 확인하려면 다음 명령어를 실행합니다.

코드 샘플

  WITH q AS (
      SELECT conrelid::regclass AS fk_table, 
            confrelid::regclass AS pk_table, 
            format('(%s)',(select string_agg(format('fk.%I', attname), ', ') 
                FROM pg_attribute a 
                JOIN unnest(conkey) ia(nr) ON ia.nr = a.attnum
              WHERE attrelid = conrelid)) AS fk_fields, 
            format('(%s)',(select string_agg(format('pk.%I', attname), ', ') 
                FROM pg_attribute a 
                JOIN unnest(confkey) ia(nr) ON ia.nr = a.attnum
              WHERE attrelid = confrelid)) AS pk_fields, 
            pg_get_constraintdef(oid)
        FROM pg_constraint
      WHERE contype='f'
  )
  SELECT format(
  $sql$
  DO $$ BEGIN RAISE NOTICE 'checking Foreign Key %3$s%1$s ==> %4$s%2$s'; END;$$;
  SELECT %1$s, %2$s 
    FROM %3$s AS fk
    LEFT JOIN %4$s AS pk ON %1$s = %2$s 
    WHERE %2$s IS NULL
      AND  %1$s IS NOT NULL  /* any NULL on FK side bypasses FK constraint by design */
  /* use limit for testing, or detecting that "there is a problem in this table */
  --  LIMIT 10
  $sql$, fk_fields, pk_fields, fk_table, pk_table
  )
    FROM q
  \gexec
  

스크립트 출력은 다음과 비슷합니다. 출력이 없으면 위반사항이 발생하지 않고 성공적으로 색인을 다시 빌드한 것입니다.

출력

  id | pk_id 
  ----+-------
      |     4
  (1 row)
  

위 출력에서 첫 번째 열에 기본 키 열이 표시되며 이 예시에서는 id라는 열입니다. 두 번째 열은 외래 키의 참조 열입니다. 즉, 상위 기본 키가 없는 pk_id=4 행이 있습니다. 이러한 키가 유효한지 확인하고 그렇지 않으면 삭제할 수 있습니다.