postgresql - 如何获取具有引用主表的外键的表的表名和列名？

Question

所以如果我有一张桌子

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
);

所以我想检查所有引用客户的表格。像这样的东西：

TABLE_NAME|COLUMN_NAME|TABLE_REFERENCES|COLUMN_REFERENCES
contacts|customer_id|customers|customer_id

所以它基本上会告诉我，在带有字段 customer_id 的表联系人中，它是对带有字段 customer_id 的表客户的外键引用。有没有办法做到这一点？

标签： postgresqlpostgresql-13

Answer 1

您正在寻找的解决方案在这篇博文中有详细说明

基本上你需要解析information_schema表table_constraints, key_column_usage, referential_constraints, table_constraints.

以下查询应该是一个很好的起点

select kcu.table_schema || '.' ||kcu.table_name as foreign_table,
       rel_tco.table_schema || '.' || rel_tco.table_name as primary_table,
       kcupk.column_name as pk_column,
       kcu.column_name as fk_column,
       kcu.constraint_name
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
          on tco.constraint_schema = kcu.constraint_schema
          and tco.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rco
          on tco.constraint_schema = rco.constraint_schema
          and tco.constraint_name = rco.constraint_name
join information_schema.table_constraints rel_tco
          on rco.unique_constraint_schema = rel_tco.constraint_schema
          and rco.unique_constraint_name = rel_tco.constraint_name
join information_schema.key_column_usage kcupk
          on rel_tco.constraint_schema = kcupk.constraint_schema
          and rel_tco.constraint_name = kcupk.constraint_name
where tco.constraint_type = 'FOREIGN KEY'
order by kcu.table_schema,
         kcu.table_name;