Checking foreign key constraints in Cloud SQL for PostgreSQL lets you identify rows
that reference missing primary keys and restore referential integrity across
your tables. Resolving these violations helps you complete index rebuild
operations after you [find and fix inconsistent
indexes](https://docs.cloud.google.com/sql/docs/postgres/find-fix-inconsistent-indexes).

To check for a foreign key where the corresponding primary key is missing, run
the following command:

### Code Sample

```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
  
```

The output of the script will be similar to the following. If there is no
output, there are no violations and you have successfully rebuilt your index.

### Output

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

In the above output, the first column shows the primary key columns, in this
example, a column named `id`. The second column is the referencing column for
the foreign key. This means there is a row, `pk_id=4`, for which a parent
primary key doesn't exist. You can decide if these keys are valid and if they
are not, you can delete them.