Query below returns foreign key constrant columns defined in a database.
select kcu.table_schema || '.' || kcu.table_name as foreign_table, '>-' as rel, rel_kcu.table_schema || '.' || rel_kcu.table_name as primary_table, kcu.ordinal_position as no, kcu.column_name as fk_column, '=' as join, rel_kcu.column_name as pk_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.key_column_usage rel_kcu on rco.unique_constraint_schema = rel_kcu.constraint_schema and rco.unique_constraint_name = rel_kcu.constraint_name and kcu.ordinal_position = rel_kcu.ordinal_position where tco.constraint_type = 'FOREIGN KEY' order by kcu.table_schema, kcu.table_name, kcu.ordinal_position;
- foreign_table - foreign table schema and name
- rel - relationship symbol implicating direction
- primary_table - primary (referenced) table schema and name
- no - position of the column in key. Single coumn keys always have 1, composite keys have 1, 2, ... n for each column of the key
- fk_column - foreign table column
- join - "=" symbol indicating join operation for pair of columns
- pk_column - primary (referenced) table column
- constraint_name - foreign key constraint name
- One row represents one foreign key column. If foreign key consists of multiple columns (composite key), each column appears separately.
- Scope of rows: all foregin keys in a database and their columns
- Ordered by foreign table schema name, table name and position
Foreign keys in pagila database with their columns: