Encontrar e corrigir violações de chaves externas

Para verificar se há uma chave externa em que a chave primária correspondente está ausente, execute o seguinte comando:

Exemplo de código

  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
  

A saída do script será semelhante ao que é exibido a seguir. Se não houver saída, não haverá violações e você terá recriado o índice.

Saída

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

Na saída acima, a primeira coluna mostra as colunas de chave primária. Neste exemplo, a coluna é chamada id. A segunda coluna é a de referência para a chave externa. Isso significa que há uma linha pk_id=4 em que não existe uma chave primária pai. É possível decidir se essas chaves são válidas. Caso não sejam, você poderá excluí-las.