Encontrar e corrigir violações de chaves externas

Para restaurar a integridade referencial no banco de dados do AlloyDB para PostgreSQL após uma reconstrução de índice ou importação de dados, execute uma consulta SQL de diagnóstico para identificar colunas de chave externa que fazem referência a chaves primárias ausentes e, em seguida, exclua ou atualize as linhas órfãs.

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 deste script é semelhante a: 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 anterior, 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.