postgreSQL外键引用查询 查询外键被那些表占用

前端之家收集整理的这篇文章主要介绍了postgreSQL外键引用查询 查询外键被那些表占用前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

根据一个表名,查询所有外键引用它的表,以及那些外键的列名
key_column_usage(系统列信息表),
pg_constraint(系统所有约束表)

SELECT  x.table_name,x.column_name
  FROM  information_schema.key_column_usage x
  INNER JOIN  (SELECT  t.relname,a.conname 
                 FROM  pg_constraint a 
                 INNER JOIN pg_class ft
                         ON ft.oid = a.confrelid 
                 INNER JOIN pg_class t 
                         ON t.oid = a.conrelid
                WHERE  a.contype = f 
                  AND  a.confrelid =
                       (select e.oid 
                          from pg_class e
                         where e.relname = xxx_table)
                ) tp 
          ON (x.table_name = tp.relname AND
              x.constraint_name = tp.conname)

猜你在找的Postgre SQL相关文章