Encuentra y corrige infracciones de claves externas

Para restablecer la integridad referencial en tu base de datos de AlloyDB para PostgreSQL después de una recompilación de índices o una importación de datos, puedes ejecutar una consulta en SQL de diagnóstico para identificar las columnas de clave externa que hacen referencia a claves primarias faltantes y, luego, borrar o actualizar las filas huérfanas.

Para verificar si hay una clave externa en la que falta la clave primaria correspondiente, ejecuta el siguiente comando:

Muestra 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

El resultado de la secuencia de comandos es similar al siguiente. Si no hay resultados, no hay incumplimientos y volviste a compilar tu índice.

Salida

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

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