Buscar y corregir infracciones de claves externas

Para buscar una clave externa en la que falte la clave principal correspondiente, ejecuta el siguiente comando:

Código de ejemplo

  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
  

El resultado de la secuencia de comandos será similar al siguiente. Si no hay ningún resultado, significa que no hay infracciones y que has recompilado el índice correctamente.

Salida

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

En el resultado anterior, la primera columna muestra las columnas de clave principal. En este ejemplo, se trata de una columna llamada id. La segunda columna es la columna de referencia de la clave externa. Esto significa que hay una fila, pk_id=4, para la que no existe una clave principal de elemento superior. Puedes decidir si estas claves son válidas y, si no lo son, puedes eliminarlas.