So, i've been search through the internet and cant seems to find the solution for this...
所以，我一直在搜索互联网，似乎无法找到解决方案......
Im trying to list all column with it's reference in postgresql. In mysql, the query would be like:
我试图在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)';
And the result is:
结果是：
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
but when im trying it in postgresql using this query:
但是当我使用这个查询在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
The result is not as i expected like in the MySQL Query... Here's the result:
结果并不像我在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
There seems to be many redudant data... When i check the query, there seems to be no diferrence in UNIQUE_CONSTRAINT_NAME at INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table...
似乎有很多的还原数据...当我检查查询时，在INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS表的UNIQUE_CONSTRAINT_NAME中似乎没有差异......
Can someone help me with this issue...?
有人可以帮我解决这个问题......？
1 个解决方案
#1
2
The issue you have here is that the referenced table has a PRIMARY KEY that is composed of multiple columns:
你在这里遇到的问题是引用的表有一个由多列组成的PRIMARY KEY：
PRIMARY KEY(kode_credential, kode_privilege, kode_feature, kode_app)
These 4 columns are joined to the other tables and then you get a Cartesian product: 4 rows for every column in every foreign key. You need to use the columns ordinal_position (of the constraint column in the referencing table - access in your case) and position_in_unique_constraint (constraint column in the referenced table - cred_access), both in the key_column_usage view. In order to pull this off, you need a self-join like so:
这4列连接到其他表，然后您得到一个笛卡尔积：每个外键中每列有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;
It's rather "magical" that MySQL can do this without specific joins like the above. Makes you wonder what other under-the-hood things could be going on...
如果没有像上面这样的特定连接，MySQL可以做到这一点相当“神奇”。让你想知道其他一些引擎盖下的事情会发生什么......
#1
2
The issue you have here is that the referenced table has a PRIMARY KEY that is composed of multiple columns:
你在这里遇到的问题是引用的表有一个由多列组成的PRIMARY KEY：
PRIMARY KEY(kode_credential, kode_privilege, kode_feature, kode_app)
These 4 columns are joined to the other tables and then you get a Cartesian product: 4 rows for every column in every foreign key. You need to use the columns ordinal_position (of the constraint column in the referencing table - access in your case) and position_in_unique_constraint (constraint column in the referenced table - cred_access), both in the key_column_usage view. In order to pull this off, you need a self-join like so:
这4列连接到其他表，然后您得到一个笛卡尔积：每个外键中每列有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;
It's rather "magical" that MySQL can do this without specific joins like the above. Makes you wonder what other under-the-hood things could be going on...
如果没有像上面这样的特定连接，MySQL可以做到这一点相当“神奇”。让你想知道其他一些引擎盖下的事情会发生什么......