Fremdschlüsselverstöße ermitteln und beheben

Wenn Sie Fremdschlüsseleinschränkungen in Cloud SQL for PostgreSQL prüfen, können Sie Zeilen identifizieren, die auf fehlende Primärschlüssel verweisen, und die referenzielle Integrität in Ihren Tabellen wiederherstellen. Wenn Sie diese Verstöße beheben, können Sie die Indexneuerstellung abschließen, nachdem Sie inkonsistente Indexe gefunden und korrigiert haben.

Führen Sie den folgenden Befehl aus, um nach einem Fremdschlüssel zu suchen, bei dem 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.