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

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 la sortie ci-dessus, la première colonne affiche les colonnes de clé primaire. Dans cet exemple, il s'agit d'une colonne nommée id. La deuxième colonne est la colonne de référence de la clé étrangère. Cela signifie qu'une ligne, pk_id=4, n'a pas de clé primaire parente. Vous pouvez décider si ces clés sont valides et, si ce n'est pas le cas, les supprimer.