🎃

PostgreSQLでFK名からテーブルを特定する

2023/11/08に公開

これだ!!

SELECT
  conname AS foreign_key_name,
  conrelid::regclass AS table_with_foreign_key,
  a.attname AS column_with_foreign_key,
  confrelid::regclass AS referenced_table,
  af.attname AS referenced_column
FROM
  pg_constraint c
  JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
  JOIN pg_attribute af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid
WHERE
  conname = 'my_fkey_name';

Discussion