Query below allows to find out columns used in foreign key constraint and corresponding them columns from primary table in a database.
select table_name || '.' || table_schema as foreign_table, '>-' rel, reference_table_schema || '.' || reference_table_name as primary_table, ordinal_position as 'no.', column_name as fk_column, '=' as join, reference_column_name as pk_column, constraint_name from v_catalog.foreign_keys order by table_schema, table_name, constraint_name, ordinal_position;
- foreign_table - foreign table name preceded by schema name
- rel - relationship symbol implicating direction
- primary_table - referenced table schema name preceded by name
- no - column position in constraint
- fk_column - foreign key column name
- join - "=" symbol indicating join operation for pair of columns
- pk_column - corresponding primary key column
- constraint_name - foreign key constraint name
- One row represents one column used by foreign key. Each column of foreign key appears separately.
- Scope of rows: all clumns used as foreign key
- Ordered by FK's table schema, FK's table name, FK's constraint name and key position within constraint