Menemukan dan memperbaiki pelanggaran kunci asing

Untuk memulihkan integritas referensial di database AlloyDB untuk PostgreSQL setelah pembangunan ulang indeks atau impor data, Anda dapat menjalankan kueri SQL diagnostik untuk mengidentifikasi kolom kunci asing yang merujuk ke kunci utama yang hilang, lalu menghapus atau memperbarui baris tanpa induk.

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 mirip dengan berikut ini. 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.