找出並修正外鍵違規

在 PostgreSQL 適用的 Cloud SQL 中檢查外鍵限制，即可找出參照遺漏主鍵的資料列，並還原資料表的參照完整性。找出並修正不一致的索引後，解決這些違規事項有助於完成索引重建作業。

如要檢查外鍵是否缺少對應的主鍵，請執行下列指令：

程式碼範例

  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 沒有父項主鍵。您可以判斷這些金鑰是否有效，如果無效，可以將其刪除。