Menemukan dan memperbaiki pelanggaran kunci asing

Memeriksa batasan kunci asing di Cloud SQL untuk PostgreSQL memungkinkan Anda mengidentifikasi baris yang mereferensikan kunci utama yang tidak ada dan memulihkan integritas referensial di seluruh tabel Anda. Menyelesaikan pelanggaran ini akan membantu Anda menyelesaikan operasi pembangunan ulang indeks setelah Anda menemukan dan memperbaiki indeks yang tidak konsisten.

Untuk memeriksa kunci asing di mana kunci utamanya yang sesuai tidak dapat ditemukan, jalankan perintah berikut:

Contoh Kode

  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

Output skrip akan terlihat serupa dengan contoh berikut. Jika tidak ada output, berarti tidak ada pelanggaran yang terjadi dan Anda telah berhasil membangun ulang indeks.

Output

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

Berdasarkan contoh output di atas, kolom pertama menunjukkan kolom kunci utama yang bernama id. Kolom kedua merupakan kolom referensi untuk kunci asing. Ini berarti ada baris, yaitu pk_id=4, yang kunci utama induknya tidak ada. Anda dapat memutuskan validitas kunci-kunci ini. Jika ternyata tidak valid, Anda dapat menghapusnya.