Rechercher et corriger les violations de clés étrangères

La vérification des contraintes de clé étrangère dans Cloud SQL pour PostgreSQL vous permet d'identifier les lignes qui font référence à des clés primaires manquantes et de restaurer l'intégrité référentielle dans vos tables. La résolution de ces cas de non-respect vous aide à effectuer des opérations de recompilation d'index après avoir recherché et corrigé les index incohérents.

Pour rechercher une clé étrangère dans laquelle la clé primaire correspondante est manquante, exécutez la commande suivante :

Exemple de code

  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
  

Le résultat du script doit ressembler à ce qui suit : S'il n'y a pas de résultat, il n'y a pas de violation et vous avez réussi à recompiler votre index.

Sortie

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

Dans le résultat ci-dessus, la première colonne affiche les colonnes de clé primaire, dans cet exemple, une colonne nommée id. La deuxième colonne est la colonne de référence de la clé étrangère. Cela signifie qu'il existe une ligne, pk_id=4, pour laquelle aucune clé primaire parente n'existe. Vous pouvez décider si ces clés sont valides et, si ce n'est pas le cas, vous pouvez les supprimer.