Fremdschlüsselverstöße ermitteln und beheben

Wenn Sie die referenzielle Integrität in Ihrer AlloyDB for PostgreSQL-Datenbank nach einer Indexneuerstellung oder einem Datenimport wiederherstellen möchten, können Sie eine SQL-Diagnoseabfrage ausführen, um Fremdschlüsselspalten zu identifizieren, die auf fehlende Primärschlüssel verweisen. Anschließend können Sie die verwaisten Zeilen löschen oder aktualisieren.

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

Ausgabe

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

In der vorherigen 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.