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:

Codebeispiel

  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 Skripts sieht in etwa so aus: Wenn keine Ausgabe erfolgt, gibt es keine Verstöße und Sie haben den Index erfolgreich neu erstellt.

Ausgabe

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

In der obigen Ausgabe werden in der ersten Spalte die Primärschlüsselspalten angezeigt, in diesem Beispiel eine Spalte mit dem Namen id. Die zweite Spalte ist die Referenzspalte 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. Wenn nicht, können Sie sie löschen.