Fremdschlüsselverstöße ermitteln und beheben

Führen Sie den folgenden Befehl aus, um nach einem Fremdschlüssel zu suchen, für den der entsprechende Primärschlüssel fehlt:

Code-Beispiel

  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
  

Die Ausgabe des Scripts sieht in etwa so aus: Wenn keine Ausgabe ausgegeben wird, gibt es keine Verstöße und der Index wurde erfolgreich neu erstellt.

Ausgabe

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

In der obigen Ausgabe enthält die erste Spalte die Primärschlüsselspalten, in diesem Beispiel eine Spalte mit dem Namen id. Die zweite Spalte ist die referenzierende Spalte für den Fremdschlüssel. Das bedeutet, dass es eine Zeile pk_id=4gibt, für die ein übergeordneter Primärschlüssel nicht existiert. Sie können entscheiden, ob diese Schlüssel gültig sind. Andernfalls können Sie sie löschen.