איתור ותיקון של הפרות של מפתח זר

בדיקת אילוצי מפתחות זרים ב-Cloud SQL ל-PostgreSQL מאפשרת לזהות שורות שמפנות למפתחות ראשיים חסרים, ולשחזר את השלמות הרפרנציאלית בטבלאות. טיפול בהפרות האלה יעזור לכם להשלים את פעולות הבנייה מחדש של האינדקס אחרי שתמצאו ותתקנו אינדקסים לא עקביים.

כדי לבדוק אם יש מפתח זר שחסר לו מפתח ראשי תואם, מריצים את הפקודה הבאה:

דוגמת קוד

  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
  

הפלט של הסקריפט אמור להיראות כך: אם לא מופיע פלט, אין הפרות והאינדקס נבנה מחדש בהצלחה.

תשובה

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

בפלט שלמעלה, העמודה הראשונה מציגה את עמודות המפתח הראשי, בדוגמה הזו, עמודה בשם id. העמודה השנייה היא העמודה שמפנה למפתח הזר. כלומר, יש שורה, pk_id=4, שאין לה מפתח ראשי הורה. אתם יכולים להחליט אם המפתחות האלה תקינים, ואם לא, אתם יכולים למחוק אותם.