如何在PostgreSQL中列出所有带有引用的列，就像在MySQL中一样？

Question

如何在PostgreSQL中列出所有带有引用的列，就像在MySQL中一样？

3

嗨，我一直在搜索互联网，但似乎找不到解决方法...

我正在尝试列出postgresql中所有带有引用的列。在mysql中，查询语句如下：

select table_schema, table_name, column_name, referenced_table_schema, referenced_table_name, referenced_column_name from informatioN_schema.key_column_usage where table_schema = '(Table Schema)';

结果如下：

setandlog    | access      | kode_privilege  | NULL                    | NULL                  | NULL
setandlog    | access      | kode_feature    | NULL                    | NULL                  | NULL
setandlog    | access      | kode_app        | NULL                    | NULL                  | NULL
setandlog    | access      | kode_credential | NULL                    | NULL                  | NULL
setandlog    | access      | username        | NULL                    | NULL                  | NULL
setandlog    | access      | kode_credential | setandlog               | cred_access           | kode_credential
setandlog    | access      | kode_privilege  | setandlog               | cred_access           | kode_privilege
setandlog    | access      | kode_feature    | setandlog               | cred_access           | kode_feature
setandlog    | access      | kode_app        | setandlog               | cred_access           | kode_app
setandlog    | access      | username        | setandlog               | login                 | username

但是当我尝试在PostgreSQL中使用以下查询时：

select r.table_schema as table_schema, r.table_name as table_name, r.column_name as column_name, u.table_schema as referenced_table_schema, u.table_name as referenced_table_name, u.column_name as referenced_column_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

结果不像在MySQL查询中我所期望的那样......以下是结果：

setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_app
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_app
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_app
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_app
setandlog    | access            | username                 | setandlog               | login                 | username

看起来有很多冗余数据……

当我检查查询时，在INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS表中的UNIQUE_CONSTRAINT_NAME没有任何差异……

有人能帮我解决这个问题吗？

- Raqasyinov

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Patrick · Accepted Answer

你在这里遇到的问题是引用的表有一个由多个列组成的PRIMARY KEY：

PRIMARY KEY(kode_credential, kode_privilege, kode_feature, kode_app)

这四列与其他表连接后，您将获得笛卡尔积：每个外键中的每个列都有4行。您需要在key_column_usage视图中使用列ordinal_position（引用表中约束列的位置 - 在您的情况下为access）和position_in_unique_constraint（引用表中的约束列 - cred_access），并进行自连接，如下所示：

SELECT k1.table_schema,
       k1.table_name,
       k1.column_name,
       k2.table_schema AS referenced_table_schema,
       k2.table_name AS referenced_table_name,
       k2.column_name AS referenced_column_name
FROM information_schema.key_column_usage k1
JOIN information_schema.referential_constraints fk USING (constraint_schema, constraint_name)
JOIN information_schema.key_column_usage k2
  ON k2.constraint_schema = fk.unique_constraint_schema
 AND k2.constraint_name = fk.unique_constraint_name
 AND k2.ordinal_position = k1.position_in_unique_constraint;

MySQL可以在没有像上面那样的特定连接的情况下完成这项操作，这真是一种“神奇”的能力。让人想知道还有哪些幕后操作...