Postgres：查找與給定 PK 關聯的所有 FK 約束

給定一些輸入 PK 列，例如ani.animals.id，我想找到與該 PK 關聯的所有 FK 約束名稱。

我還需要每個特定 FK 約束所在的表和模式的名稱。

---

( schema_name.table_name, FK_constraint_name )所以基本上我正在尋找所有FK_constraint_name引用輸入PK的元組。

例如，輸入 PKani.animals.id具有：

• fk_vet_animal在表中ani.vets
• fk_person_animal在表中usr.people

---

任何想法如何解決它？

我的最終目標是，給定一些 PK，刪除所有找到的 FK 約束，並使用ON UPDATE CASCADE.

以下查詢對您有用嗎？

SELECT nr.nspname AS table_schema,
       r.relname AS table_name,
       c.conname AS constraint_name,
       split_part ( split_part ( pg_get_constraintdef ( c.oid ), '(', 2 ), ')', 1 ) AS column_names,
       nr2.nspname AS r_table_schema,
       r2.relname AS r_table_name,
       split_part ( split_part ( pg_get_constraintdef ( c.oid ), '(', 3 ), ')', 1 ) AS r_column_names,
       CASE c.confmatchtype
           WHEN 'f' THEN 'MATCH FULL '
           WHEN 'p' THEN 'MATCH PARTIAL '
           WHEN 'u' THEN 'MATCH NONE '
           ELSE ''
           END
           || CASE c.confupdtype
               WHEN 'c' THEN 'ON UPDATE CASCADE '
               WHEN 'n' THEN 'ON UPDATE SET NULL '
               WHEN 'd' THEN 'ON UPDATE SET DEFAULT '
               WHEN 'r' THEN 'ON UPDATE RESTRICT '
               WHEN 'a' THEN 'ON UPDATE NO ACTION '
               ELSE ''
               END
           || CASE c.confdeltype
               WHEN 'c' THEN 'ON DELETE CASCADE'
               WHEN 'n' THEN 'ON DELETE SET NULL'
               WHEN 'd' THEN 'ON DELETE SET DEFAULT'
               WHEN 'r' THEN 'ON DELETE RESTRICT'
               WHEN 'a' THEN 'ON DELETE NO ACTION'
               ELSE ''
               END AS constraint_rule,
       d.description AS comments
   FROM pg_class r
   INNER JOIN pg_namespace nr
       ON ( nr.oid = r.relnamespace )
   INNER JOIN pg_constraint c
       ON ( c.conrelid = r.oid )
   LEFT OUTER JOIN pg_description d
       ON ( d.objoid = c.oid )
   INNER JOIN pg_catalog.pg_class r2
       ON ( r2.oid = c.confrelid )
   INNER JOIN pg_namespace nr2
       ON ( nr2.oid = r2.relnamespace )
   WHERE r.relkind = 'r'
       AND c.contype = 'f'
       --AND nr.nspname = ?
       --AND r.relname = ?
       AND nr2.nspname = ?
       AND r2.relname = ?

引用自：https://dba.stackexchange.com/questions/279654